SQL Server BCP utility best separator character to use

Question

I am sure many would came across this scenario.

Whenever we use SQL Server BCP utility to import/export data to-from delimited text files, which is the best "Separator Character" to use. We have Note-text kind of fields which can have , and now we found | & ~ is also used by our users in those fields. Which character in your opinion has least chances of user ever used. Or you have better alternative to deal with this issue.

Update- This software is being developed in UK-English users.

Answer 1

The best way is to whatever character you select to use as separator should not come in any of your Text field, If your text field contain your separator character then replace it before you place your text into bcp export file. ^ or | are rarely used characters so use anyone of them as separator character and put check in your code to replace your separator character from your text field with some other character so it will not make any confusion.

Answer 2

Use non-printing characters - ASCII code 2 and ASCII code 3 are commonly used as START and STOP delimiters (STX/ETX) and are certain not to appear in user data.