3

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

1 Answer 1

4

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.

11
  • Ok, so I was on the right track, thanks so much for the info. When I use the value directly from the DB I get this ㈀ ㄀㐀 㜀㈀㐀   㔀㘀㠀㠀 Apr 9, 2015 at 18:37
  • SELECT CONVERT(NVARCHAR(MAX),VARDATA),VARDATA was used for the above Apr 9, 2015 at 18:38
  • Ok, so the data was original from Oracle, I need to do the hex conversion in that db first. Apr 9, 2015 at 18:40
  • 1
    What does "stored as hex" mean? What is the actual data type, and what is the exact insert/update statement that put it there? What is the exact statement that leads to all those Japanese characters? Apr 9, 2015 at 19:00
  • 1
    I don't know anything about how Oracle handles binary conversions. But if you want SQL Server to correctly translate a binary value to 201407240005688309, you need to make sure you're starting with the right binary value. Apr 9, 2015 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.