Is there any way to obtain an hexadecimal dump of a string in SQL Server? It'd be useful to troubleshoot character set and collation issues.
In MySQL you'd do SELECT HEX('€uro') and in Oracle you'd do SELECT DUMP('€uro') FROM DUAL.
views:
37answers:
2
+3
A:
SELECT CAST('€uro' AS VARBINARY(4))
Returns
0x8075726F
On my default collation.
Edit. Just noticed the Unicode tag
SELECT CAST(N'€uro' AS VARBINARY(8))
Returns
0xAC20750072006F00
Martin Smith
2010-09-17 10:17:35
Lovely! Do you know what the byte order depends on? Being picky, I'd prefer `20AC` rather than `AC20`, but in any case I want to make sure what order to expect.
Álvaro G. Vicario
2010-09-17 11:11:23
@Álvaro - No I don't know the answer to that I'm afraid.
Martin Smith
2010-09-17 11:38:53
@Álvaro: This is a bit wicked, and I suppose it could fail for strings that use higher-plane Unicode glyphs, but it seems to meet your byte-order requirement:SELECT SUBSTRING(CAST(CAST(0xFEFF + SUBSTRING(CAST(CAST(@v as XML) as VARBINARY(MAX)),3,DATALENGTH(CAST(@v as XML))-2) AS XML) AS VARBINARY(MAX)),3,DATALENGTH(CAST(@v as XML))-2)
Steve Kass
2010-09-18 04:23:53