CODEWITHSUNDEEP
castingsql-server-2008-r2binary
Anjali
Anjali

Reputation: 1718

Cast Varchar to Binary in SQL Server 2008 R2?

For one value I am getting two different values when I am casting nvarchar to binary.

For the value A12875 I am getting a result like 0x4131323837350000000000000000000000000000

I get this when I am using a select statement.

If the same statement is executed in a stored procedure I am getting a result like this

0x2000410031003200380000000000000000000000

I am using the same query

select cast('A12875' as binary(20))

What is the difference?`

Upvotes: 0

Views: 307

Answers (1)

Ray Toal
Ray Toal

Reputation: 88378

The difference is character encoding. A character encoding is a way to represent characters as bytes.

The characters you have, and their unicode code points are

A    code point 41
1    code point 31
2    code point 32
8    code point 38
7    code point 37
5    code point 35

If you use UTF-8 (or Latin-1 or even ASCII) to encode this string you will get, left padded in a field of 20 bytes:

41 31 32 38 37 35 00 00 ... 00

But there are other character encodings. It looks like when you run a stored procedure, it is choosing UTF-16LE as the encoding, and that somehow a space character ends up in front. In UTF-16LE the code point 41 is represented as

41 00

because it would normally be 0041 but the bytes are reversed. So you would expect:

41 00 31 00 32 00 38 00 37 00 35 00  ... 00 00

The space character is code point 20 so it is represented as 20 00. I don't know why they put the space up front; it could be a funny way of making a byte order mark, i.e. 2000 for little endian and 0020 for big endian.

At any rate you should look at the SQL Server documentation to see how to use character encodings when characters are converted to bytes. You know, whenever you try to covert characters to bytes, you must specify an encoding. Maybe a default is there, but in general characters->bytes make no sense without an encoding. In your scenario, the two different environments used two different defaults.

Upvotes: 1

Related Questions