CODEWITHSUNDEEP
sqlsql-server
Paul Wetter
Paul Wetter

Reputation: 73

SQL Select Converting to ASCII/varchar from Hex

I have data stored in a column in my SQL Server table like the following:

0x003600380032003200330031002D003400

I know the output for this should be:

682231-4

Can someone help me with the SQL code for the conversion?

Something like:

 select converttochar (0x003600380032003200330031002D003400)

Returns: 682231-4

I'm not sure if I am looking at hex or binary conversion or what....

Upvotes: 2

Views: 4264

Answers (4)

Martin Smith
Martin Smith

Reputation: 453047

It is difficult to see what the exact encoding issue is based on the sample data.

I imagine Larnu is correct and this is just UCS-2 encoded data that for some reason has had a leading null byte added.

Another interpretation is that this is UCS-2 encoded data encoded with a different endianess - so each double byte needs to be reversed (though then there is still a surplus null byte at the end).

As long as each alternate byte is 0x00 it doesn't much matter which case is correct. To strip out the null bytes and cast to varchar you can also use

SELECT REPLACE(0x003600380032003200330031002D003400, 0x00, '' COLLATE Latin1_General_Bin)

For the limited character set shown in the question these will all have the same effect. If the raw data can have non ASCII characters this approach will likely fall down.

Upvotes: 5

Thom A
Thom A

Reputation: 95564

This actually looks like you have an nvarchar with a leading 00 at the start. This is messy, but would work:

SELECT CONVERT(nvarchar(30),CONVERT(varbinary(60),STUFF(CONVERT(VARCHAR(60), 0x003600380032003200330031002D003400,1),3,2,''),1));

This strips out the leading 00 characters, making the value 0x3600380032003200330031002D003400, which is the nvarchar value for N'682231-4'.

It strips the value out by converting the varbinary to a varchar but using the style code 1, this means you have a varchar with the value '0x3600380032003200330031002D003400'; allowing you to perform string manipulations. Then I use STUFF to remove the character and CONVERT with style code 1 to make it a varbinary again.

DB<>Fiddle

Alternatively, you could remove all the null character tuples and convert. This looks way messier as you can't just blanket replace '00' (or rather I don't feel it's "safe" to), so I put the characters into their tuples, and rebuild:

DECLARE @YourBinary varbinary(60) = 0x003600380032003200330031002D003400;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(60) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3)
SELECT CONVERT(varchar(60),CONVERT(varbinary(60),(SELECT V.SS + ''
                                                  FROM Tally T
                                                       CROSS APPLY(VALUES(SUBSTRING(CONVERT(varchar(60),@YourBinary,1),(T.I*2)+1,2)))V(SS)
                                                  WHERE V.SS NOT IN ('00','')
                                                  FOR XML PATH(''),TYPE).value('.','varchar(60)'),1));

This idea might be better though, if some of your values have a leading 00 and others don't. Of course, this solution also assumes the end value is a varchar, not an nvarchar.

Upvotes: 5

Ajeet Verma
Ajeet Verma

Reputation: 1123

I had also same issue some ago and the used the below statement for convert and my problem resolved that time ..

SELECT CONVERT(VARCHAR(60), 0x003600380032003200330031002D003400);

Please try to run this and see whether your problem is resolved or not?

Upvotes: 1

juergen d
juergen d

Reputation: 204756

SELECT CONVERT(VARCHAR(60), 0x003600380032003200330031002D003400);

Demo

Upvotes: 1

Related Questions