如何十六进制转换为字符串在SQL Server
这里是我的十六进制输入如何十六进制转换为字符串在SQL Server
0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e
预期成果是:
<<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>
如果它是只有一小集,总是需要更换一个变量,那么你也可以替换他们这样的ASCII码:
declare @string varchar(max) = '0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e';
select @string = replace(@string,hex,chr)
from (values
('0x3c','<'),
('0x3e','>'),
('0x5b','['),
('0x5d',']'),
('0x2f','/')
) hexes(hex,chr);
select @string as string;
返回:
string
------
<<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>
如果有更多的字符,或者硬编码被压低了?
然后循环的替代也将得到这一结果:
declare @string varchar(max) = '0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e';
declare @loopcount int = 0;
declare @hex char(4);
while (patindex('%0x[0-9][a-f0-9]%',@string)>0
and @loopcount < 128) -- just safety measure to avoid infinit loop
begin
set @hex = substring(@string,patindex('%0x[0-9][a-f0-9]%',@string),4);
set @string = replace(@string, @hex, convert(char(1),convert(binary(2), @hex, 1)));
set @loopcount = @loopcount + 1;
end;
select @string as string;
如果你想在一个UDF包,那么你甚至可以在查询中使用它。
这是我正在寻找的,真的很感谢你的帮助! –
@NileshBankar谢谢。如果有更多的代码需要替换,我已经包含了非硬编码版本。 – LukStorms
你的字符串是混合十六进制和字符的数据,所以你需要对它进行解析用代码。一个棘手的部分是将0xCC
子字符串转换为它表示的字符。首先假设它是二进制的,然后转换为char。使用递归所有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]>>
感谢您的帮助! –
https://dba.stackexchange.com/questions/132996/convert-hexadecimal-to-varchar –
SELECT CONVERT(VARCHAR(MAX),0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e); –
此链接doenst帮助我。需要得到确切的结果>这个结果。 –