Excluding varbinary prefix? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Excluding varbinary prefix?

Author

Topic

codrgiii
Starting Member

29 Posts

Posted - 2010-04-27 : 01:07:13

I need to compare a varChar(32) column with a varBinary(50) column, but the problem is that the varbinary data comes with a prefix of '0x' at the start of it, so how am i able to remove the prefix and continue on comparing data from the varChar column?

Example;

varChar(32) example column - 
60cd54a928cbbcbb6e7b5595bab46a9e

varBinary(50) example column - 
0x60cd54a928cbbcbb6e7b5595bab46a9e  <-need to remove the 0x from the varBinary so i can compare both columns

The varChar column i'm trying to compare is md5 hashed, so is the varBinary but i can't compare it because of the prefix, so i'll appreciate any guidance i can on how to solve this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 01:40:01

did you try casting varbinary to varchar and replacing 0x?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-27 : 04:07:47

quote:
did you try casting varbinary to varchar and replacing 0x?

you can't do this on 2005 or earlier (though it works fine on 2008 with a style value of 2). The conversion to VARCHAR does different things between (2005 and earlier) and 2008

I had exactly the same problem in an earlier post (way earlier)

Here's Peso's resultant blog on the subject: (probably all you need will be here)
http://weblogs.sqlteam.com/peterl/archive/2010/01/27/Convert-binary-value-to-string-value.aspx

I got round it but using and undocumented function call. details are here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138869

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

codrgiii
Starting Member

29 Posts

Posted - 2010-04-27 : 12:18:37

Solved!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources