Conversions between Character and Binary Values in Katmai

Prior to Microsoft SQL Server 2008, when you used the CONVERT
function to convert a character string to a binary type, you got the ASCII
codes of the characters. Similarly, when converting from a binary value to a
character string, you got the characters represented by the ASCII codes in
the binary value.

If you had character strings representing hex digits in a binary value (e.g.,
'0x53514C'), and wanted to produce binary values that look the same
(0x53514C), or the other way around, you had to use custom convoluted
solutions. Such types of conversions are required for tasks such as importing
data.

Microsoft SQL Server 2008 provides a nifty solution to this need by
introducing new styles for the CONVERT function when converting
between character strings and binary values. When using style 0, you get the
default behavior as in previous versions of Microsoft SQL Server. For
example, run the following code:

SELECT  CONVERT(VARCHAR(3), 0x53514C, 0) AS [Bin to Char 0],  CONVERT(VARBINARY(3), 'SQL', 0)  AS [Char to Bin 0];

And you will get the following output:

Bin to Char 0 Char to Bin 0------------- -------------SQL           0x53514C

Styles 1 and 2 introduce the new functionality. Style 1 includes the '0x'
prefix in the result string when converting a binary value to a characterstring,
and requires it in the input string when converting from a string to a binary
value. Style 2 does not include the '0x' prefix in the result string when
converting a binary value to a character string, and does not require it in the
input string when converting from a string to a binary value. To demonstrate
the new styles, run the following code:

SELECT  CONVERT(VARCHAR(8)  , 0x53514C  , 1) AS [Bin to Char 1],  CONVERT(VARBINARY(3), '0x53514C', 1) AS [Char to Bin 1],  CONVERT(VARCHAR(6)  , 0x53514C  , 2) AS [Bin to Char 2],  CONVERT(VARBINARY(3), '53514C'  , 2) AS [Char to Bin 2];

And you will get the following output:

Bin to Char 1 Char to Bin 1 Bin to Char 2 Char to Bin 2------------- ------------- ------------- -------------0x53514C      0x53514C      53514C        0x53514C

Learn more: Using Large CLR UDTs in SQL Server 2008

Cheers,
BG