Sql-server – Converting VARBINARY HEX to VARCHAR

sql-server-2008t-sql

I have a fun problem,

I have data stored in SQL Server 2008 VARDATA column as Hex.

I need to see what this translated to string is.

I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a Varchar value of the hex exactly the same as it is in the VARDATA.

If I put the value that is in my VARDATA into an online converter I get the exact value I expect back.

Any help would be appreciated.

Details.

Example of the Hex is 

0x003200300031003400300037003200340030003000300035003600380038003300300039

It should work out to.

201407240005688309

EDIT: some additional information. This data starts out in ORACLE it is in a RAW type column.

We bring the data to SQL Server 2008 via Linked Servers using the below open query.

Select TABNAME, VARKEY, DATALN, VARDATA 
from sapsr3.KAPOL

When I use the suggested function on this SQL Server 2008 table below.

Select CONVERT(NVARCHAR(MAX),VARDATA) 
from KAPOL

I get these symbols: 

㈀　　㄀　㄀　㄀　　　　　　㜀㜀㠀㠀

If I do the conversion of HEX in Oracle first and then try and bring it over I get an empty column.

Select TABNAME, VARKEY, DATALN, utl_raw.cast_to_nvarchar2(hextoraw(VARDATA)) VARDATA 
from sapsr3.KAPOL

Lastly, in the first instance with no conversion in the oracle table the data type for the column VARDATA is VARBINARY

When I try the conversion in Oracle first is NVARCHAR

Best Answer

You need to have a distinction between varchar and nvarchar. There is a big difference. If I try to create varbinary from your "should work out to" value:

SELECT CONVERT(VARBINARY(64), '201407240005688309')

I get this value, which looks kind of like the value you're converting, but not quite:

0x323031343037323430303035363838333039

So now if I change the input to be nvarchar:

SELECT CONVERT(VARBINARY(64), N'201407240005688309')

Now I do get the value you wanted:

0x3200300031003400300037003200340030003000300035003600380038003300

So if I take that value and try to convert it to nvarchar instead of varchar, we're back where we started:

SELECT CONVERT(NVARCHAR(MAX),
  0x3200300031003400300037003200340030003000300035003600380038003300300039)

Result:

201407240005688309

The online converter you're using must be using Unicode behind the scenes. You need to do that, too.

Related Question