SQL Server 2008’s T-SQL Features

It was possible to achieve similar behavior prior to SQL Server 2008, but you had to use scalar UDFs—so the solution was much slower. SQL Server 2005 and SQL Server 2000 provide a built-in UDF called fn_varbintohexstr that does the binary to character conversion. Here’s an example for using the function:

SELECT sys.fn_varbintohexstr(0x4775696E6E657373);

Internally, this function calls a more flexible function called fn_varbintohexsubstring. This function accepts four arguments. The first indicates whether to include the 0x prefix in the output (1) or not (0). The second is the binary value. The third indicates in which byte to start extracting the substring (use 1 for beginning), and the fourth indicates how many bytes to consider (0 for all). So the above call to the function fn_varbintohexstr is equivalent to the following call to fn_varbintohexsubstring:

SELECT sys.fn_varbintohexsubstring(1,0x4775696E6E657373,1,0);

If you’re curious about the T-SQL definition of the function, you can find it by running the following code:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.fn_varbintohexsubstring'));

As you can see, the function is quite long and convoluted. If you need to perform such conversions, you’ll appreciate the simplicity, elegance, and efficiency of the new option using the CONVERT function.

Pre-2008 versions of SQL Server did not provide an option for converting the other way around; namely, from character to binary. As long as you don’t need to implement this logic in a function, and need to operate on a single value, you can use a neat trick that I learned from my friend Ron Talmage. You simply construct a dynamic batch where you concatenate the character representation of the binary value as part of the code as an assignment of a binary value to a parameter, like so:

Char to Bin pre-2008

Using dynamic SQL

DECLARE

  @char AS NVARCHAR(20),

  @bin AS VARBINARY(10),

  @sql AS NVARCHAR(500);

SET @char = N'0x4775696E6E657373';

SET @sql = N'SET @result = ' + @char + N';'

EXEC sp_executesql

  @stmt = @sql,

  @params = N'@result AS VARBINARY(10) OUTPUT',

  @result = @bin OUTPUT;

SELECT @bin;

But if you want to be able to invoke the conversion in a query against a table, you must implement it as a UDF. You can use the function fn_chartobin that is provided in Listing 3 for this purpose.

The function extracts from the input string one pair of digits at a time (since each pair represents a byte), produces the corresponding binary byte, and concatenates it to the result binary string. The function can accept the input with or without the 0x prefix. To test the function, run the following code:

SELECT dbo.fn_chartobin('0x4775696E6E657373');

The output will be the binary value 0x4775696E6E657373.

It’s easy to overlook some of the T-SQL enhancements in SQL Server 2008, especially because Microsoft hasn’t made a lot of fuss about them. Three small features that you might find handy include the enhanced VALUES clause, improved ISO week number calculation, and the ability to convert character to binary and binary to character values while preserving the hexadecimal digits. Next month I’ll cover some additional SQL Server 2008 features that you might find useful.