sql - 如何在Sql服务器中将十六进制转换为字符串

Question

这是我的十六进制输入

0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e

预期输出是:

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

Answer 1

您的字符串混合了十六进制和字符数据，因此您需要使用代码对其进行解析。一个棘手的部分是将 0xCC 子字符串转换为其表示的字符。首先假装它是二进制的，然后转换为字符。使用递归迭代所有0xCC子字符串

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;

返回

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