Nilesh Bankar Nilesh Bankar - 5 months ago 51
SQL Question

How to convert Hex to String in Sql server

Here is my hex input

0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e

Expected output is : <<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>

Answer Source

Your string is mixing hex and char data, so you need to parse it with a code. A tricky part is converting 0xCC substring to a char it represents. First pretend it's binary and then cast to char. Using recursion to iterate over all 0xCC substrings

declare @imp nvarchar(max) = '0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e';

with cte as (
select replace(col, val, cast(convert(binary(2), val, 1) as char(1))) as col
 from (
   -- sample table
   select @imp as col
   ) tbl
 cross apply (select patindex('%0x__%',tbl.col) pos) p
 cross apply (select substring(col,pos,4) val) v
union all
select replace(col, val, cast(convert(binary(2), val, 1) as char(1))) as col
 from cte 
 cross apply (select patindex('%0x__%',col) pos) p
 cross apply (select substring(col,pos,4) val) v
where pos > 0
)
select * 
from cte
where patindex('%0x__%',col) = 0;

Returns

col
<<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>