2

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....

Improve this question
3
  • Microsoft SQL 2016 Commented Dec 31, 2019 at 7:25
  • Try this: dba.stackexchange.com/questions/132996/… Commented Dec 31, 2019 at 8:09
  • The problem is that this data isn't ASCII - The 0x00 is a null character that is interpreted as a string terminator. This is why you are seeing blank strings. Maybe SQL Fiddle strips this out. But the data isn't a valid SQL Server nvarchar encoding either . All the byte pairs need to be reversed 0x0036 would need to be 0x3600 to be interpreted as 6. If all the characters are from the ASCII set then could just do a replace of 0x00 to strip it out (using a binary collate clause) Commented Dec 31, 2019 at 8:52

4 Answers 4

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is perfectly simple. Yes, all the characters will be ASCII just like my very limited example. The community here continues to amaze me. Thank you!
5

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.

Improve this answer

5 Comments

SELECT REPLACE(0x003600380032003200330031002D003400, 0x00, '' COLLATE Latin1_General_Bin)
You should post that as an answer, @MartinSmith ;) Hadn't even occured to me to think about collations.
It is just a suggested simplification to yours not a new answer (in response to "you can't just blanket replace '00'")
Certainly easier for others to understand though, I still think it's a new answer, as even if the result is the same, the method is different; and it's good to see those as answers as well.
Ahh, it's because I'm going down the pessimistic route of the OP may have data like 0x003600300000003900, @MartinSmith . They probably don't, but considering that the OP is treating their sample value as a varchar (not an nvarchar) suggests it can have many (sequential) null characters. Call me a cynic. ;)
1 
SELECT CONVERT(VARCHAR(60), 0x003600380032003200330031002D003400);

Demo

Improve this answer

3 Comments

That seems to only work if the 00 are removed from the hex. Otherwise, I get a blank result. 0x3638323233312D34
Interesting... Running it on my SQL 2016 server produces different results. Blank with the zeros and the expected result without...
I too have the problem that @PaulWetter describes, and i would actualyl expect that. 00 in a hex in a null character, so something like 003600380032 has 3 null characters in it. DB<>Fiddle won't even run the above; and I find it to be more reliable that SQL Fiddle at times.
1

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?

Improve this answer

1 Comment

I'm getting blank in result. How its working for you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.