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?
This question is related to
sql
sql-server
string
varbinary
Try:
DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;
SELECT CONVERT(varchar(max),@varbinaryField,2),
@varbinaryField
UPDATED: For SQL Server 2008
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
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)
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)');
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
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
Source: Stackoverflow.com