CODEWITHSUNDEEP
sql-serverbinaryvarbinary
deldev
deldev

Reputation: 1376

Why SQL binary convert function results a non-0101... value?

Why when I use the command in SQL Server 2005

select convert(varbinary(16),N'123')

results 0x310032003300 and not 1111011?

Upvotes: 1

Views: 396

Answers (1)

AaronLS
AaronLS

Reputation: 38367

Basically each letter of '123' gets converted to it's UCS-2(basically the ASCII value padded to make it a double byte) value in the three double bytes of 0x3100, 0x3200, 0x3300, and concatenated together in a varbinar.

Hopefully that answers why you see this behavior. If you convert to an int first you may see what you were perhaps hoping for instead:

select convert(varbinary(16),cast(N'123' as int))

produces hex value 0x0000007B which in binary is 1111011

See http://www.asciitable.com/ the entry for numeric 3, the hex representation is 0x33 which corresponds to the same entry in unicode: http://www.ssec.wisc.edu/~tomw/java/unicode.html (this pattern does not necessarily hold true for all ASCII/unicode characters, but does for the 10 integers).

Upvotes: 1

Related Questions