THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

In SQL Server 2008 The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values

SQL Server 2008 CTP 6 has enhanced the convert function when you do conversion between binary and character hexadecimal values
There are 3 styles:
Style 0 works the same as on 2005 it converts binary to varchar, if you have 0x64656E6973 then you will get 'denis'
Style 1 converts binary to varchar but the values stay the same, if you have 0x64656E6973 you will get '0x64656E6973'
Style 2 strips the 0x but leaves the rest of the values, if you have 0x64656E6973 you will get '64656E6973'

Try it out

When you run this

SELECT CONVERT(varbinary(5),'denis')

The output will be 0x64656E6973. Now we can use that in the select statements below

SELECT CONVERT(varchar(18), 0x64656E6973, 0) AS 'Style 0' --denis
SELECT CONVERT(varchar(18), 0x64656E6973, 1) AS 'Style 1' --0x64656E6973
SELECT CONVERT(varchar(18), 0x64656E6973, 2) AS 'Style 2' --64656E6973

When you run the code above on SQL Server 2005 all 3 select statements return 'denis'


It is all in Books On Line for CTP 6, it would have been nice if they also included the sys.dm_tran_commit_table dmv or the sys.dm_exec_trigger_stats dmv. I have no clue what the sys.dm_tran_commit_table dmv is supposed to do  :-(

Published Wednesday, February 27, 2008 12:02 PM by Denis Gobo
Filed under:



Adam Machanic said:

You beat me to the punch.  I have this feature at the top of my list of things to blog about soon... it's a great enhancement!

February 27, 2008 11:38 AM

Denis Gobo said:

Now your list is a little shorter, maybe you should add sys.dm_tran_commit_table to it instead ;-)

February 27, 2008 12:37 PM

Scott R. said:


I don't have CTP6 loaded yet, but I ran your samples on CTP5.  They work as you describe for styles 0 and 1 but not style 2 (so just style 2 is new to CTP6).  I noted that other higher number styles are accepted by both SQL 2005 and SQL 2008 CTP5 (tested styles 3 and 4; does not produce errors of "invalid style" or similar error), but just returns the text result of "denis" as you had stated for style 0.

To sum up:

-  SQL 2005 processes style 0 only, and treats all other styles as style 0

-  SQL 2008 CTP5 processes styles 0 and 1, and treats all other style #s as style 0

-  SQL 2008 CTP6 processes styles 0, 1 and 2, and treats all other style #s as style 0 (as tested by Denis)

Thanks for sharing this discovery.

Scott R.

February 27, 2008 2:28 PM

Denis Gobo said:


style 4 is used when converting dates so it won't throw an error (4 for YY and 104 for YYYY)

SELECT CONVERT(varchar(18), getdate(), 4) AS 'Style 4' --27.02.08

SELECT CONVERT(varchar(18), getdate(), 104) AS 'Style 4' --27.02.2008

did you know that you can convert money when using styles? Run this and look at the commas and decimal places

declare @money money

select @money = 12345.123

SELECT CONVERT(varchar(18), @money, 0) AS 'Style 0' --12345.12

SELECT CONVERT(varchar(18), @money, 1) AS 'Style 1' --12,345.12

SELECT CONVERT(varchar(18), @money, 2) AS 'Style 2' --12345.1230

February 27, 2008 2:41 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement