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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)