We help IT Professionals succeed at work.

Locate hidden characters in a MSSQL field

In our inventory master table it looks like we have some duplicate item numbers but they are in fact different because one has invalid hidden characters at the end of the visible item number.

How can I display the hex representation of the item number so that I know what to correct?
Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Downloadeth a free trial of UltraEdit, File:Open and open a flat file that contains your data (copy-paste might work too), hit Control-H, and bammo now you can see Hex characters and their ANSI codes.

Author

Commented:
I can do that but was hoping to do something in SQL since I may need to look at thousands of items for potential issues.
Top Expert 2012
Commented:
Have you tried SELECT ASCII(RIGHT(YourColumnNameGoesHere, 1))

I suspect the answer is 10 as in CrLf or just Lf
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
one of these might suit:

SELECT MASTER.dbo.fn_varbintohexstr(CAST('string here' AS VARBINARY))

|                 COLUMN_0 |
|--------------------------|
| 0x737472696e672068657265 |

Open in new window


SELECT CONVERT(VARBINARY(MAX), 'string here')

|                                   COLUMN_0 |
|--------------------------------------------|
| 115,116,114,105,110,103,32,104,101,114,101 |

Open in new window