10

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Álvaro González
  • 142,137
  • 41
  • 261
  • 360

1 Answers1

28
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
  • 438,706
  • 87
  • 741
  • 845
  • 1
    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 González Sep 17 '10 at 11:11
  • @Álvaro - No I don't know the answer to that I'm afraid. – Martin Smith Sep 17 '10 at 11:38
  • 1
    @Á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 Sep 18 '10 at 04:23