SQL Server converting varbinary to string
Solution 1
Try:
DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;
SELECT CONVERT(varchar(max),@varbinaryField,2),
@varbinaryField
UPDATED: For SQL Server 2008
Solution 2
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)
Solution 3
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
Solution 4
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)');
Solution 5
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
strike_noir
Updated on July 09, 2022Comments
-
strike_noir almost 2 years
I want to do conversion in T-SQL from a
varbinary
type to string typeHere is an example :
First I got this
varbinary
0x21232F297A57A5A743894A0E4A801FC3
And then I want to convert it to
21232f297a57a5a743894a0e4a801fc3
How to do this?
-
ZedZip over 11 yearsHow to do the same convert for field but not for variable?
-
Anssssss over 10 yearsTo do the same but for a column rather than a variable, you would use the sql:column function (technet.microsoft.com/en-us/library/ms191214.aspx). So it would be cast('' as xml).value('xs:hexBinary(sql:column("someColumnNameHere"))'
-
ConstantineK over 9 yearsFor some reason on SQL Server 2012, Converting didnt work, but Cast did the trick??? Thanks!
-
MÇT over 9 yearsIt can also be used when retrieving directly from a table:
SELECT CONVERT(VARCHAR(MAX), MyBinaryField, 2) FROM MyTable WHERE ID = 123456
-
Graeme over 9 yearsIf you wish to preserve the '0x' prefix, use the 1 style Flag in the CONVERT expression (instead of 2).
SELECT CONVERT( VARCHAR(MAX), MyBinaryField, 1) FROM MyTable WHERE ID = 123456;
. -
Graeme over 9 yearsSimple CAST may not work by default because the hex expression is converted to ASCII/UNICODE. Use CONVERT with a style flag (1 or 2).
-
Jalal over 9 yearsAnd how check it for null inline?
DECLARE @varbinaryField varbinary(max); SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3; SELECT CONVERT(varchar(max), ISNULL(@varbinaryField, 'value is NULL'), 2), @varbinaryField
raise errorImplicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
-
Frondor almost 8 yearsAny idea on how to update that field back with the string re-converted back to varbinary?