Systems Engineering and RDBMS

SQL Server 2008: Converting Binary and Character Values

Posted by decipherinfosys on July 31, 2008

In one of our post, we had covered how to convert a hext string to a varbinary and vice-versa in SQL Server 2005. In SQL Server 2005, if you use the convert function to convert a character string to a varbinary, you will get the ASCII codes of the characters and likewise if you tried to convert from a varbinary to a string using convert, you would have gotten the characters represented by the ASCII codes in the binary value. That is why one needed to use different means like the one we have shown in the previous post. In SQL Server 2008, one can simply use the CONVERT function to do the same thing.

The CONVERT function now has new styles that allows to make this a possibility. The binary styles can have one of the three possible values: 0, 1 or 2. When using 0, the behavior is the same as SQL Server 2005 and prior releases i.e. it translates ASCII characters to binary bytes or binary bytes to ASCII characters.

When you use the value of 1 and are converting a string to a binary, you need to have the “0x” in the input string. And when you use the style of 2, you do not need the “0x” in the input string when converting a string to a binary. And when converting a binary to a string, when using style 1, it includes the “0x” prefix in the resulting string – when you do the same with style 2, it does not include the prefix. Here is an example to help make sense out of the above statements:

SELECT
CONVERT(NVARCHAR(34) , 0xc23eed6b65c93e44a41a2818e274194f, 1) AS BINARY_TO_STRING_STYLE1,
CONVERT(VARBINARY, ‘0xc23eed6b65c93e44a41a2818e274194f’, 1) AS STRING_TO_BINARY_STYLE1,
CONVERT(NVARCHAR(34), 0xc23eed6b65c93e44a41a2818e274194f, 2) AS BINARY_TO_STRING_STYLE2,
CONVERT(VARBINARY, ‘c23eed6b65c93e44a41a2818e274194f’, 2) AS STRING_TO_BINARY_STYLE2

And here are the results:

BINARY_TO_STRING_STYLE1            STRING_TO_BINARY_STYLE1              BINARY_TO_STRING_STYLE2            STRING_TO_BINARY_STYLE2
---------------------------------- ------------------------------------ ---------------------------------- ----------------------------------
0xC23EED6B65C93E44A41A2818E274194F 0xC23EED6B65C93E44A41A2818E274194F   C23EED6B65C93E44A41A2818E274194F   0xC23EED6B65C93E44A41A2818E274194F

2 Responses to “SQL Server 2008: Converting Binary and Character Values”

  1. Rich said

    If the field in the database is varbinary how do you get it back to the original text?
    Data Value = 0x3900380037002D00360035002D003400330032003100. This is in a SQL 2005 database.

    Thanks

  2. Rich said

    I tried convert and cast and all that shows in the result is one character. the 9
    Shouldn’t I be able to get back all the characters in one result.

    Rich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: