Varbinary to String on SQL Server

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

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 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 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 varbinary to string within query

It turns out after investigating further than some compression had been done at the application level in C# before writing this data to the database as BLOBs. Because of this there are no standard SQL server functions to translate the data into human readable form, I would need to reverse the process that compressed the data in an application.

I have left this question here rather than deleting in-case anyone else has a similar issue in the future.

---

Related Topics