SQL Server converting varbinary to string

Question

I want to do conversion in T-SQL from a varbinary type to string type  Here is an example    First I got this varbinary  0x21232F297A57A5A743894A0E4A801FC3   And then I want to convert it to   21232f297a57a5a743894a0e4a801fc3   How to do this

User · Answer

Here is a simple example I wrote to convert and convert back using the 2 convert methods, I also checked it with a fixed string

declare @VB1 VARBINARY(500),@VB2 VARBINARY(500),@VB3 VARBINARY(500)

declare @S1 VARCHAR(500)

SET @VB1=HASHBYTES('SHA1','Test')

SET @S1=CONVERT(varchar(500),@VB1,2)

SET @VB2=CONVERT(varbinary(500),@S1,2)

SET @VB3=CONVERT(varbinary(500),'640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA',2)

SELECT @VB1,@S1,@VB2,@VB3

IF @VB1=@VB2 PRINT 'They Match(2)'

IF @VB1=@VB3 PRINT 'They Match(3)'

PRINT str(Len(@VB1))

PRINT str(Len(@S1))

PRINT str(Len(@VB2))

SET @VB1=HASHBYTES('SHA1','Test')

SET @S1=CONVERT(varchar(500),@VB1,1)

SET @VB2=CONVERT(varbinary(500),@S1,1)

SELECT @VB1,@S1,@VB2

IF @VB1=@VB2 PRINT 'They Match(1)'

PRINT str(Len(@VB1))

PRINT str(Len(@S1))

PRINT str(Len(@VB2))

and the output

||| 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA|640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA|0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA|0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

(1 row(s) affected)

They Match(2)

They Match(3)

    20
    40
    20

|| 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA|0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA|0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

(1 row(s) affected)

They Match(1)

    20
    42
    20

User · Answer

If you want to convert a single VARBINARY value into VARCHAR  STRING  you can do by declaring a variable like this   DECLARE  var VARBINARY MAX  SET  var   0x21232F297A57A5A743894A0E4A801FC3 SELECT CAST  var AS VARCHAR MAX     If you are trying to select from table column then you can do like this   SELECT CAST myBinaryCol AS VARCHAR MAX   FROM myTable

User · Answer

This works in both SQL 2005 and 2008   declare  source varbinary max   set  source   0x21232F297A57A5A743894A0E4A801FC3  select cast    as xml  value  xs hexBinary sql variable   source       varchar max

User · Answer

Try   DECLARE  varbinaryField varbinary max   SET  varbinaryField   0x21232F297A57A5A743894A0E4A801FC3   SELECT CONVERT varchar max   varbinaryField 2     varbinaryField   UPDATED  For SQL Server 2008

User · Answer

I looked everywhere for an answer and finally this worked for me   SELECT Lower Substring MASTER dbo Fn varbintohexstr 0x21232F297A57A5A743894A0E4A801FC3   3  8000     Outputs to  string       21232f297a57a5a743894a0e4a801fc3   You can use it in your WHERE or JOIN conditions as well in case you want to compare match varbinary records with strings

User · Answer

I know this is an old question  but here is an alternative approach that I have found more useful in some situations  I believe the master dbo fn varbintohexstr function has been available in SQL Server at least since SQL2K  Adding it here just for completeness   Some readers may also find it instructive to look at the source code of this function   declare  source varbinary max   set  source   0x21232F297A57A5A743894A0E4A801FC3  select varbin source    source  string result   master dbo fn varbintohexstr   source

[sql] SQL Server converting varbinary to string

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to varbinary