Here is my hex input
0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e
Expected output is :
<<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>
-
dba.stackexchange.com/questions/132996/…Shwetabh Shekhar– Shwetabh Shekhar2017-08-17 07:58:31 +00:00Commented Aug 17, 2017 at 7:58
-
SELECT CONVERT(VARCHAR(MAX),0x3c0x3c0x5bIMG0x5d0x5bSIZE0x5dHALF0x5b0x2fSIZE0x5d0x5bID0x5d540x5b0x2fID0x5d0x5b0x2fIMG0x5d0x3e0x3e);Shwetabh Shekhar– Shwetabh Shekhar2017-08-17 08:04:07 +00:00Commented Aug 17, 2017 at 8:04
-
This link doenst help me. Need to get exact <<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>> this result.Nilesh Bankar– Nilesh Bankar2017-08-17 08:06:18 +00:00Commented Aug 17, 2017 at 8:06
Add a comment
|
2 Answers
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]>>
1 Comment
Nilesh Bankar
Thanks for your help!!
If it's for only a small set of ascii codes that always need replacement in a variable, then you can also replace them like this:
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;
Returns:
string
------
<<[IMG][SIZE]HALF[/SIZE][ID]54[/ID][/IMG]>>
If there are more characters, or hardcoding is frowned upon?
Then looping a replacement will also get that result:
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;
If you would wrap it in a UDF then you can even use it in a query.
2 Comments
Nilesh Bankar
This is what I am looking for , Really appreciated your help !!
LukStorms
@NileshBankar Thanks. I've included a non-hardcoded version for if there's more codes to replace.