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?
Watch Question

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

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.


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
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
one of these might suit:

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

|                 COLUMN_0 |
| 0x737472696e672068657265 |

Open in new window


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

Open in new window