Converting percent-decoded data, or characters to int

Say I have a percent encoded expression like www%2emysite%2ecom. An unencoded representation would be www.mysite.com.

I have written a tsql regex function to parse an input string and replace any legal percent-encoded substring with its ascii equivalent, returning the unencoded version of the input string. However I'm stuck on one crucial step: converting a percent-encoded substring (ex: '%2e') or its hexadecimal string representation ('0x2e') to an integer.

I've tried CONVERT(bigInt, '0x2e') but of course that isn't working.

What is the best way to go about this? I've been all over the sql server books online, search engines, and other forums. I can't get past this one step. Am I on the right track, or should I look at it a different way?

Without knowing more about the rest of your code, you can do this: exec ('select CONVERT(int, ' + '0x2e' + ') '), though not in a t-sql function (but I don't know how you are doing regex in a T-SQL function)

|||

you have to convert the varchar value to binary value..

You can't direcly assign this value from the varchar varibale/column, if you convert the varchar value it will create a binary equalent of the string..

I once used the following logic to overcome this issue,

Declare @.ValueString as Varchar(100)
Set @.ValueString = '0x2e'

Declare @.Value as VarBinary
Declare @.sql as NVarchar(1000)

Select @.sql = N'select @.val=' + @.ValueString
Exec sp_executesql @.sql, N'@.val varbinary output', @.Value output

Select char(convert(bigint, @.value))

--Result: .

Here we are assinging the Binary value directly to the VarBinary variable, with out using the convert function..But i am not sure how it will help you to create a generic function(since inside the funcation you can't able to call sp_executesql)..

|||Thank you.

I don't know how you are doing regex in a T-SQL function

My t-sql regex find function works via vbScript RegExp class. I'm sure it's not the most efficient ,but it does work very well.

I still haven't made any progress with the current issue. I don't understand why a character expression that is also a well-formed hexadecimal number can't be easily converted into an integer. Sigh...