Converting string hex pair into character

I need to parse a text string, stored as varchar(1000), like the one shown
below
RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
and covert the hexadecimal character codes into their corresponding
characters. For example, the above should come out to
RootFolder=/C14/LDI Documentation/Document Library
I cannot figure out a way to pass in a string value for the hex code to the
CHAR function. For example
SELECT CHAR('0x2f')
returns a datatype conversion error. Trying to manually CAST it produces the
same error.
The idea was to iterate through the string, finding all hex-pair represented
characters, and covert them, like so
WHILE (CHARINDEX('%', @.csuristem) <> 0 )
BEGIN
SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
@.csuristem) + 1,2) +
RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
END% is a reserved character in SQL (the wildcard character) and needs to be
escaped.
Try this:
(CHARINDEX('[%]', @.csuristem)
And of course:
CHAR(0x2f)
"Be it a number, no quotes shall be used. No where, no how, so there."
-- Luke, 5, 12
The tough part is actually converting the HEX codes to characters in T-SQL.
Perhaps this example can be of help:
http://milambda.blogspot.com/2005/0...a.blogspot.com/|||leave out the quotes: char(0x2f)
but another way is to add a character mapping table, e.g.
create table charmap (
asc_val tinyint not null unique,
htm_hex char(3) not null unique,
chr_val char(1) not null unique
)
insert into charmap (asc_val, htm_hex, chr_val)
values (32, '%20', ' ')
insert into charmap (asc_val, htm_hex, chr_val)
values (33, '%21', '!')
etc.
and then use the way mssql assignes variables to do it in one shot, e.g.
declare @.x varchar(1000)
set @.x = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
select @.x = replace(@.x, htm_hex, chr_val)
from charmap
select @.x
Mark Williams wrote:
> I need to parse a text string, stored as varchar(1000), like the one shown
> below
> RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
> and covert the hexadecimal character codes into their corresponding
> characters. For example, the above should come out to
> RootFolder=/C14/LDI Documentation/Document Library
> I cannot figure out a way to pass in a string value for the hex code to th
e
> CHAR function. For example
> SELECT CHAR('0x2f')
> returns a datatype conversion error. Trying to manually CAST it produces t
he
> same error.
> The idea was to iterate through the string, finding all hex-pair represent
ed
> characters, and covert them, like so
> WHILE (CHARINDEX('%', @.csuristem) <> 0 )
> BEGIN
> SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
> SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
> @.csuristem) + 1,2) +
> RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
> END
>
>|||I know that CHAR(0x2f) will work, but that won't work as a solution to this
particular problem.
To put it another way, look at this
DECLARE @.string varchar(50)
SET @.string = 'mark%2fwilliams'
SELECT STUFF(@.string, CHARINDEX('%', @.string), 3, CHAR(SUBSTRING(@.string,
CHARINDEX('%', @.string) + 1, 2)))
I want to be able to programmatically convert hexadecimal specified
characters in a string.
I wrote my own udf to convert a hex string into an integer
CREATE FUNCTION udfHexcharToInt (@.hexstring varchar(512))
RETURNS int
AS
BEGIN
DECLARE @.halfbyte CHAR(1)
DECLARE @.halfbyteint int
DECLARE @.power int
DECLARE @.counter int
DECLARE @.outputint int
SET @.power = LEN(@.hexstring) - 1
SET @.counter = 0
SET @.outputint = 0
WHILE (@.counter <= @.power)
BEGIN
SET @.halfbyteint = CASE
WHEN ISNUMERIC(SUBSTRING(@.hexstring, @.counter + 1, 1)) = 1 THEN
CAST(SUBSTRING(@.hexstring, @.counter + 1, 1) AS int) * POWER(16, @.power -
@.counter)
WHEN ASCII(LOWER(SUBSTRING(@.hexstring, @.counter + 1, 1))) BETWEEN 97 AND
102 THEN (ASCII(LOWER(SUBSTRING(@.hexstring, @.counter + 1, 1))) - 87) *
POWER(16, @.power - @.counter)
ELSE -1
END
IF (@.halfbyteint < 0)
BEGIN
RETURN @.halfbyteint
END
SET @.outputint = @.outputint + @.halfbyteint
SET @.counter = @.counter + 1
END
RETURN @.outputint
END
I can then feed this into CHAR. The statement I started with now becomes
DECLARE @.string varchar(50)
SET @.string = 'mark%2fwilliams'
SELECT STUFF(@.string, CHARINDEX('%', @.string), 3,
CHAR(dbo.udfHexcharToInt(SUBSTRING(@.string, CHARINDEX('%', @.string) + 1, 2))
))
which works out. The charmap table sounds good too, and I will probably put
together another udf that will covert all of the hex pair characters in a
string at once.
If you couldn't tell, I'm doing this to clean up the content of a W3C
website log file.
"Trey Walpole" wrote:

> leave out the quotes: char(0x2f)
> but another way is to add a character mapping table, e.g.
> create table charmap (
> asc_val tinyint not null unique,
> htm_hex char(3) not null unique,
> chr_val char(1) not null unique
> )
> insert into charmap (asc_val, htm_hex, chr_val)
> values (32, '%20', ' ')
> insert into charmap (asc_val, htm_hex, chr_val)
> values (33, '%21', '!')
> etc.
> and then use the way mssql assignes variables to do it in one shot, e.g.
> declare @.x varchar(1000)
> set @.x = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
> select @.x = replace(@.x, htm_hex, chr_val)
> from charmap
> select @.x
> Mark Williams wrote:
>|||Here's another approach:
go
CREATE FUNCTION hex2char (
@. char(2)
) returns char as begin
set @. = upper(@.)
declare @.a tinyint
declare @.lookup char(15) set @.lookup = '123456789ABCDEF'
return (
select char(sum(wt*charindex(substring(@.,pos,1)
,@.lookup)))
from (select 1 as wt, 2 as pos union all select 16,1) as T
)
end
go
DECLARE @.string varchar(50)
SET @.string = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
DECLARE @.p int
SET @.p = patindex('%[%]%',@.string)
WHILE @.p > 0 BEGIN
SET @.string =
STUFF(@.string,@.p,3,dbo.hex2char(substring(@.string,@.p+1,2)))
SET @.p = patindex('%[%]%',@.string)
END
SELECT @.string
go
drop function hex2char
Steve Kass
Drew University
Mark Williams wrote:

>I need to parse a text string, stored as varchar(1000), like the one shown
>below
> RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
>and covert the hexadecimal character codes into their corresponding
>characters. For example, the above should come out to
>RootFolder=/C14/LDI Documentation/Document Library
>I cannot figure out a way to pass in a string value for the hex code to the
>CHAR function. For example
>SELECT CHAR('0x2f')
>returns a datatype conversion error. Trying to manually CAST it produces th
e
>same error.
>The idea was to iterate through the string, finding all hex-pair represente
d
>characters, and covert them, like so
>WHILE (CHARINDEX('%', @.csuristem) <> 0 )
>BEGIN
> SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
> SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
>@.csuristem) + 1,2) +
> RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
>END
>
>
>|||Brilliant. Thanks!
--
"Steve Kass" wrote:

> Here's another approach:
>
> go
> CREATE FUNCTION hex2char (
> @. char(2)
> ) returns char as begin
> set @. = upper(@.)
> declare @.a tinyint
> declare @.lookup char(15) set @.lookup = '123456789ABCDEF'
> return (
> select char(sum(wt*charindex(substring(@.,pos,1)
,@.lookup)))
> from (select 1 as wt, 2 as pos union all select 16,1) as T
> )
> end
> go
> DECLARE @.string varchar(50)
> SET @.string = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Librar
y'
> DECLARE @.p int
> SET @.p = patindex('%[%]%',@.string)
> WHILE @.p > 0 BEGIN
> SET @.string =
> STUFF(@.string,@.p,3,dbo.hex2char(substring(@.string,@.p+1,2)))
> SET @.p = patindex('%[%]%',@.string)
> END
> SELECT @.string
> go
> drop function hex2char
> Steve Kass
> Drew University
> Mark Williams wrote:
>
>