Mitch Wheat - Treat the cause, not the symptoms!

I was updating several TSQL scripts today containing ARGB values and thinking (not for the first time) that it was a shame I couldn’t use the Hex values directly (eg 0xFFB40000) rather than converting to the harder to manage base10 integer equivalent (-4980736).

The SQL Server 2008 release updated the CONVERT() function to be able to do just that!

     CONVERT(varbinary(4), '0xFFB40000', 1)

The last Style parameter governs the the conversion.

From Books Online:

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

 

Value	Output
0 (default)	Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1. If the data_type is a binary type, the characters 0x are added to the left of the result.
1, 2	If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised. If the length of the converted expression is greater than the length of the data_type the result will be right truncated. Fixed length data_types that are larger then the converted result will have zeros added to the right of the result. If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated. If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits. The characters 0x will be added to the left of the converted result for style 1.