Hex to ascii conversion

[mkal]

I am trying to take a hex value like 0x3A, 0x2f and convert it to it's ascii equivlent. I have code below that works but it needs to run inside of a function and since only other functions and extended stored procedures can be executed from within a function it is failing.

The function takes a string (comes from IIS log file) that is a long URL similiar to the following:

http://someURL.com/View.htm?&entryLocation=http://www.someURL.com/,

so ascii characters like ":" and "/" have been converted to their hex counterparts %3A or 0x3A in tsql, %2F or 0x2F and I need to convert them back.

declare @v varchar(20), @sql nvarchar(MAX), @ch varchar(20)
select @v = '3A'
select @sql = 'SELECT @ch = convert(varchar, 0x' + @v + ')'
print @sql
EXEC sp_executesql @sql, N'@ch varchar(30) OUTPUT', @ch OUTPUT
SELECT @ch,upper(substring(master.sys.fn_varbintohexstr(convert(varbinary, @ch)),3, 2*len(@ch)))

Anyone have another way of doing this that doesn't require dynamic sql? I'd rather not write this as an extended stored procedure.

If SSIS has the ability to do this via a transform task that will work too.

Thanks

 

[mkal]

I should have also said that the function loops through the string one character at a time finding and replacing the hex values with the ascii character and rebuilding the string.

I'm sure there are better ways to do this, just nothing comes to mind right now.

Input:

http://someURL.com/View.htm?&entryLocation=http://www.someURL.com/

Output:

http://someURL.com/View.htm?&entryLocation=http://www.someURL.com/

I could probably do a find and replace in this instance but I don't know ahead of time what the hex value will be.

 

[softhemc]

Possibly something like this?

create table #hextoint (intval int identity (0, 1), hexval varchar(1),)
insert into #hextoint
select '0'
union all select '1'
union all select '2'
union all select '3'
union all select '4'
union all select '5'
union all select '6'
union all select '7'
union all select '8'
union all select '9'
union all select 'A'
union all select 'B'
union all select 'C'
union all select 'D'
union all select 'E'
union all select 'F'

declare @source nvarchar(max), @thishex nvarchar(3), @thisval int, @position int
set @source = '[URL unfurl="true"]http://someURL.com/View.htm?&entryLocation=http%3A%2F%2Fwww.someURL.com%2F'[/URL]
print @source
set @position = charindex('%', @source)
while @position <> 0
begin
	set @thishex = substring(@source, @position, 3)
	select @thisval = (select intval from #hextoint where upper(hexval) = substring(@thishex, 2, 1)) * 16
	select @thisval = @thisval + (select intval from #hextoint where upper(hexval) = substring(@thishex, 3, 1))
	set @source = replace(@source, @thishex, char(@thisval))
	set @position = charindex('%', @source)
end
print @source

drop table #hextoint

 

[hmckillop]

What you have listed

The function takes a string (comes from IIS log file) that is a long URL similiar to the following:

http://someURL.com/View.htm?&entryLocation=http://www.someURL.com/,

so ascii characters like ":" and "/" have been converted to their hex counterparts %3A or 0x3A in tsql, %2F or 0x2F and I need to convert them back.

is URL encoding.

a function for URL encoding and URL unencoding can be found here.
Encoding -

http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urlencode.aspx

Decoding -

http://www.codeproject.com/KB/reporting-services/SQLURLDecode.aspx

Ideally though, you should do this in the application side as there are many different methods and helper classes already embedded e.g. in .NET System.Web.HttpUtility.UrlEncode

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mkal]

Thanks for the help guys, I have it working!