Suppose you have column ID in table T. Try the following:
Select ID, CAST(ID as Varbinary(255)) as Hex From T.
I find this to be sufficient in almost every case (and I strongly prefer to see the values in hex!).
Just my 2 cents. Normally, I'm looking for characters out of the ordinary. I also normally have a whole lot of data in files that I want to import or already have imported to check. I don't want to be eyeballing literally a billion or more hexadecimal bytes of information. The method in the article and the cast, in the absence of everything else, would require such eyeballing.
That being said, why not just bring a Tally function into play an let the computer quickly isolate that which you've been eyeballing? Here's a very simple example for just one string.
DECLARE @SomeString VARCHAR(MAX) = 'The quick brown fox jumpedá over the lazy dogs.
You should have seen what the cats did.'
SELECT StringPosition = t.N
,Character = SUBSTRING(@SomeString,t.N,1)
,ASCII_Dec = RIGHT(1000+ASCII(SUBSTRING(@SomeString,t.N,1)),3)
,ASCII_Hex = CONVERT(CHAR(2),CONVERT(BINARY(1),(SUBSTRING(@SomeString,t.N,1))),2)
FROM dbo.fnTally(1,DATALENGTH(@SomeString)) t
WHERE ASCII(SUBSTRING(@SomeString,t.N,1)) NOT BETWEEN 32 AND 126
Here are the results from that code.
If you don't already have a "Tally" function, the one I use for production code is at the link in my signature line below.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)