CODEWITHSUNDEEP
sqlt-sqlsql-server-2005binarymd5
deldev
deldev

Reputation: 1376

Why TSQL convert a function's result in one way and a character string to other way?

I try this command in SQL Server 2005 to obtain a MD5 from '123':

select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123' )), 3, 32)

and I get this result: 

202cb962ac59075b964b07152d234b70

I want to convert to binary format, 

select 
   convert(varbinary(16), SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123')), 3, 32))

And I get this result: 

0x32003000320063006200390036003200

Why does this code:

select convert(varbinary(16), '202cb962ac59075b964b07152d234b70')

result in a different value?

0x32303263623936326163353930373562

Upvotes: 0

Views: 126

Answers (1)

Stephan
Stephan

Reputation: 6018

"Regular Character Type" vs Unicode

This performs a conversion from Nvarchar(Unicode) to Varbinary

select convert(varbinary(16),SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',  '123' )),3,32))

By default, putting text in single quotes uses regular character types like Char or Varchar. This performs a conversion from Varchar("Regular Data Type") to Varbinary

select convert(varbinary(16),'202cb962ac59075b964b07152d234b70')

Try this:

SELECT CONVERT(varbinary(16), N'202cb962ac59075b964b07152d234b70')

The "N" before the quote defines the value as Nvarchar(Unicode) and you get your desired value

0x32003000320063006200390036003200

Hope this helps!

Upvotes: 2

Related Questions