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

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

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

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

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

User · Answer

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

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

[sql] SQL Server converting varbinary to string

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to varbinary