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?
Who is Participating?
Anthony PerkinsCommented:
Have you tried SELECT ASCII(RIGHT(YourColumnNameGoesHere, 1))

I suspect the answer is 10 as in CrLf or just Lf
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
jdr0606Author 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.
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.