Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In working through the Advent of Code and solving some of the problems in SQL, I found that I needed to take hex values and convert them to strings. In other words, I had a value like this:

select @hex = 0x3c044139f4fe36d7df0f4e87f948fc52

and I needed to determine if the first few characters (5 or 6), were 0s. In other words, I wanted to look at this part of the data above as a string.

3c044

I thought this would be simple. I tried this

select @value = CAST( @hex as varchar(50))

That’s my default, as it reads nicely. However the returned this:

<A9ôþ6×ßN‡ùHüR

That’s strange. I then tried CONVERT:

select @value = convert( varchar(50), @hex)

I got the same result. Why am I not getting the same value as a string? I looked at a few other code samples from others, and they looked the same, so I checked the documentation for CONVERT. I saw this:

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.

Under the table, the information for 1 or 2 as a style has this:

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.

…

The characters 0x will be added to the left of the converted result for style 1.

All of that essentially means that if I use the default, 0, or have nothing, I get the binary data converted to to the binary bytes in ASCII. If I use 1 or 2, I get the string. Here’s a shot of the difference:

Two lessons. First, learn the data types and how they convert. Second, read the documentation carefully when things don’t work as expected.

Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL