SQL Server Converting Varbinary to String

varbinary to string on SQL Server

"Converting a varbinary to a varchar" can mean different things.

If the varbinary is the binary representation of a string in SQL Server (for example returned by casting to varbinary directly or from the DecryptByPassPhrase or DECOMPRESS functions) you can just CAST it

declare @b varbinary(max)
set @b = 0x5468697320697320612074657374

select cast(@b as varchar(max)) /*Returns "This is a test"*/

This is the equivalent of using CONVERT with a style parameter of 0.

CONVERT(varchar(max), @b, 0)

Other style parameters are available with CONVERT for different requirements as noted in other answers.

SQL Server converting varbinary to string

Try:

DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;

SELECT CONVERT(varchar(max),@varbinaryField,2), 
@varbinaryField

UPDATED:
For SQL Server 2008

why is CONVERT string to VARBINARY in SQL Server only converting first character?

The reason is that when you insert you're converting a Unicode (nvarchar(xx)) string to varbinary. Then when you select you're converting to varchar(xx). If you convert to nvarchar(xx) it will work fine.

For example:

• inserting 'this is a test' as varbinary(30) results in 0x7468697320697320612074657374.

• inserting N'this is a test' as varbinary(30) results in 0x74006800690073002000690073002000610020007400650073007400.

So when you convert back, if you specify varchar(30) the first 00 will truncate the string.

This works fine for me:

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') ) 
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2

and so does this

delete from Table_2

insert Table_2 (Test) values( CONVERT(varbinary(30), 'this is a test') )
select * from Table_2
select CONVERT(varchar(30), test) from Table_2

SQL Server convert varbinary to string

You might use the built-in function fn_varbintohexstr:

DECLARE @SomeHexString VARBINARY(MAX)=CAST('This is just some text, which should be a HEX-string' AS VARBINARY(MAX));
SELECT @SomeHexString;

SELECT 'This is concatenated: ' + sys.fn_varbintohexstr(@SomeHexString)

This function existed in 2005 already, but was limited in length. Should be fine with your 2008 environment...

Convert utf-8 encoded varbinary(max) data to nvarchar(max) string

SQL-Server does not know UTF-8 (at least all versions you can use productivly). There is limited support starting with v2014 SP2 (and some details about the supported versions)
when reading an utf-8 encoded file from disc via BCP (same for writing content to disc).

Important to know:

VARCHAR(x) is not utf-8. It is 1-byte-encoded extended ASCII, using a codepage (living in the collation) as character map.

NVARCHAR(x) is not utf-16 (but very close to it, it's ucs-2). This is a 2-byte-encoded string covering almost any known characters (but exceptions exist).

utf-8 will use 1 byte for plain latin characters, but 2 or even more bytes to encoded foreign charsets.

A VARBINARY(x) will hold the utf-8 as a meaningless chain of bytes.

A simple CAST or CONVERT will not work: VARCHAR will take each single byte as a character. For sure this is not the result you would expect. NVARCHAR would take each chunk of 2 bytes as one character. Again not the thing you need.

You might try to write this out to a file and read it back with BCP (v2014 SP2 or higher). But the better chance I see for you is a CLR function.

Convert varbinary value to string to get the same value

When you look at the official docs for convert you find that for binary data there is a style option of 0, 1, 2. Style option 1 gives the value in hex format.

DECLARE @RFID INT = 1292202724;

SELECT CONVERT(VARBINARY(8), @RFID) AS 'VARBINARY_VALUE';

SELECT CONVERT(NVARCHAR(15), CONVERT(VARBINARY(8), @RFID), 1 /* style 1 */) AS 'STRING_VALUE'; --Using Convert

Convert string representation of VARBINARY to VARBINARY value

So, generally speaking you will use convert to go to and from varbinary and varchar. You'll want to use 2 for the style instead of 1 to get the string representation. If you want the literal conversion, use the default of 0.

DB FIDDLE

declare @v varbinary(128) = (select cast('ThisIsMyPassword' as varbinary(128)))

select
    @v as Val
    ,convert(varchar(max),@v,2) as String
    ,convert(varchar(max),@v,0) as Literal

See the docs on when to use the styles here

Convert UTF-8 varbinary(max) to varchar(max)

I don't like this solution, but it's one I got to (I initially thought it wasn't working, due to what appears to be a bug in ADS). One method would be to create a new database in a UTF8 collation, and then pass the value to a function in that database. As the database is in a UTF8 collation, the default collation will be different to the local one, and the correct result will be returned:

CREATE DATABASE UTF8 COLLATE Latin1_General_100_CI_AS_SC_UTF8;
GO
USE UTF8;
GO
CREATE OR ALTER FUNCTION dbo.Bin2UTF8 (@utfbinary varbinary(MAX))
RETURNS varchar(MAX) AS
BEGIN
    RETURN CAST(@utfbinary AS varchar(MAX));
END
GO
USE YourDatabase;
GO
SELECT UTF8.dbo.Bin2UTF8(0x48656C6C6F20F09F988A);

This, however, isn't particularly "pretty".

---

Related Topics