0

I have a sql query, select distinct(name) from customers with (nolock) and it returns the text I want in SSMS, ie "Smith, John", etc.

However, when I get the string value from my DataTable in C#, I get back strange Control Characters at the beginning of my string, like \u001f\u001f\u001fSmith, John

enter image description here

Where is this coming from? Is it bad data in my database, or am I missing some steps related to character encoding or collation?

If anything culture or collation-related needs to be done, I'd prefer to do it from either from within the SQL query (without introducting a new SQL function) or from C#, since I can't control what values are placed in the database, I can only read from them.

UPDATE:

I have another VB.NET application which queries these names for a different purpose. This other program does NOT return the printing control characters in the DataTable. This leads me to believe there is something wrong with my SQLAdapter or SQLCommand implementation. Any ideas?

The table collation is SQL_Latin1_General_CP1_CI_AS.

turkinator
  • 905
  • 9
  • 25
  • 1
    It's likely [non-printable control characters](http://www.fileformat.info/info/unicode/char/1f/index.htm) in your data. SSMS is probably just ignoring them rather than showing a placeholder. – D Stanley Jan 09 '17 at 19:53
  • 1
    They are unicode control characters. How did you enter this data into your database table? If you have to deal with them you could use this strategy to strip them out in c#. http://stackoverflow.com/questions/6799631/removing-control-characters-from-a-utf-8-string – Rick S Jan 09 '17 at 19:54
  • @RickS Names are imported from an external system through a process I have no control over.Thanks, I had found that post earlier. It does seem to work, and I plan to use it as my workaround if I can't find a means to solve the problem otherwise. I feel like I should be able to define something about Collation in my SqlConnection or SqlDataAdapter objects. Any ideas? – turkinator Jan 09 '17 at 19:58
  • 1
    I don't think collation is the problem. All collation does is determine the relative order of strings based on case, accents, etc. It does not alter the presence of specific characters in your data. I think you have non-printable characters in your data that need to be removed, either from the data directly or as part of your query by `TRIM`ming the column. – D Stanley Jan 09 '17 at 19:59
  • Question updated. I have another (VB.NET) application which runs the same query and does NOT return the printing control characters. Any ideas? – turkinator Jan 09 '17 at 22:14

0 Answers0