Express Hexadecimal binary as char

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Express Hexadecimal binary as char

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 09:49:17

Hi all,

I want to do something simple (hopefully) but can't find out how -- also not sure how to look either. Having a hard time expressing what I want in keywords.

This is what I need:

I need to express the binary result of an SHA1 HASH brought back from the HASHBYTES function as a literal string for each hexadecimal value.

For instance I need to turn the output of this:

SELECT(HASHBYTES('SHA1', 'password')

(which is 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8) into the string

'5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8'

You can't do this with a simple CAST.

I'm sure there is an easy way but I'm having a hard time searching for it.

Hope this makes sense.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 09:56:21

Addendum -- using CONVERT to CHAR with a style of 2 works fine on 2008. It doesn't work the same way on 2005 (I get a character rpresentation).

For instance taken from books online for 2008


SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 1, binary to character'

Gives me '4E616d65' which is exactly what I want.

But on 2005 I get: 'Name'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 09:57:41

There's a BINtoHEX function or somesuch, isn't there?

EDIT:

I thought there was a way back in SQL 2000, maybe not (but this is a workaround)

http://support.microsoft.com/kb/104829

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 10:00:09

Think I've found it!


SELECT sys.fn_sqlvarbasetostr(HASHBYTES('SHA1', 'password'))

I was a bit floored when the CONVERT behaviour I was using on 2008 suddenly wasn't working for me in a different environment!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 10:05:10

hmm. I can't find the documentation for this function in my BOL

Does anyone know if this function is deprecated?

Generally I find the documentation on SQL SEVER to be very, very good but there are odd times where I just can't find the information I am looking for.

Cheers for the reply Kristen.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:07:35

fn_sqlvarbasetostr ... undocumented (Why? Goodness knoly knows, what sort of "threat" is that, compared to its usefulness)

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:17:02

Madhivanan will be along in a minute ... "Format in your front end application"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 10:26:58

ah -- but it isn't. I'm trying to replicate the way that an application is storing hashes for passwords. I've got a bulk update to do and the application still has to be able to understand the hashes afterwards.

OFC the application SHOULD be storing the hashes as a binary value rather than as string but that's another matter entirely!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:31:19

"the application SHOULD be storing the hashes as a binary value rather than as string"

Ah ... I get it now. One of the DEVs probably tossed a coin and it came up "Strings"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-26 : 10:56:49

Sadly, they do that a lot! Though thank the SQL gods that they at least treat dates as dates.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 02:49:28

quote:
Originally posted by Transact Charlie

Hi all,

I want to do something simple (hopefully) but can't find out how -- also not sure how to look either. Having a hard time expressing what I want in keywords.

This is what I need:

I need to express the binary result of an SHA1 HASH brought back from the HASHBYTES function as a literal string for each hexadecimal value.

For instance I need to turn the output of this:

SELECT(HASHBYTES('SHA1', 'password')

(which is 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8) into the string

'5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8'

You can't do this with a simple CAST.

I'm sure there is an easy way but I'm having a hard time searching for it.

Hope this makes sense.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Am I missing something?

SELECT substring(cast(sys.fn_sqlvarbasetostr(HASHBYTES('SHA1', 'password')) as varchar(1000)),3,100)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 03:29:08

"Am I missing something?"

fn_sqlvarbasetostr is undocumented? (in both SQL 2005 and SQL 2008 ... which is worrying because it clearly wasn't just a DOC omission in SQL 2005.

Why do MS add an innocuous feature like this and make it unsupported and prone to dropping from future versions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 03:33:38

Why not use XML?

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET	@bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT	@bin AS OriginalValue,
	CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 03:39:07

Blog post here http://weblogs.sqlteam.com/peterl/archive/2010/01/27/Convert-binary-value-to-string-value.aspx

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 03:46:49

<<
Why do MS add an innocuous feature like this and make it unsupported and prone to dropping from future versions?
>>

Only they can answer

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:36:14

Yeah, it was a rhetorical question Madhi

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-27 : 05:12:58

quote:
Originally posted by Peso

Blog post here http://weblogs.sqlteam.com/peterl/archive/2010/01/27/Convert-binary-value-to-string-value.aspx

N 56°04'39.26"
E 12°55'05.63"

Well,

Obviously it works and it's very clever.....

But it seems a long way to go to replicate an undocumented function call in 2005.

Also it is a little harder to understand for code review purposes by someone else. (OFC as without a little internet searching you can't find and documentation on the function then...)

As you don't need to do either of these in 2008 and up is there any possibility of a 2005 service pack removing the function?

EDIT: Guess the big + for the XML is that it is future proofed. Code written that uses this in 2005 will still *work* in 2008 and future versions albeit in a completely ass backward way on those platforms. Guess I'll use the XML if I ever need to do this again.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 05:42:39

There are Sprocs out there that do BIN to HEX conversion. Would that do you? If so you would have a future-proof solution.

sp_helptext 'fn_sqlvarbasetostr'

will show you the source ... adopt it as your own UDF for future-proofing? Put a note in your diary to compare it against every future service pack and new version to make sure you adopt any bug fixes that the Vendor introduces

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources