Click here to Skip to main content
15,116,516 members
Articles / Database Development / SQL Server
Posted 25 Jan 2007


22 bookmarked

URL Decoding in pure T-SQL

Rate me:
Please Sign up or sign in to vote.
4.33/5 (2 votes)
25 Jan 20074 min read
How to decode a URLEncoded URL with T-SQL


This is a short article on how to perform URLDecoding in T-SQL. It's something I recently had to do and I struggled to find an adequate solution in the public domain, so I decided to write my own. The way it is done is somewhat unusual in that the hexadecimal numbers that denote encoded characters are not treated as numbers, but rather as strings, most of the way through the process. I'll start by laying out the code in its entirety, as it's not very long, and then I'll go through it step by step, explaining as I go.

The Code

@input nvarchar(4000)
RETURNS nvarchar(4000)
declare @char nvarchar(2)
declare @asc nvarchar(2)
declare @asc2 nvarchar(2)

while (charindex('%', @input) > 0)
 set @char=(select substring(@input, charindex('%', 
    @input) +1, 2))
 if (isnumeric(substring(@char, 1, 1)))>0
  set @asc=(select cast(substring(@char, 1, 1)
    as int))*16
  set @asc=(select ascii(cast(substring(@char, 1, 1) 
    as char)))-55
  set @asc=(select @asc*16)
 if (isnumeric(substring(@char, 2, 1)))>0
  set @asc=(select cast(@asc as int) + 
    (select cast(substring(@char, 2, 1) as int)))
  set @asc2=(select ascii(cast(substring(@char, 2, 1) 
    as char)))-55
  set @asc=(select cast(@asc as int) + 
    (select cast(@asc2 as int)))
 set @input=
    (select substring(@input, 0, charindex('%', @input))) 
    + char(@asc) + (select substring(@input, charindex('%', 
    @input)+3, len(@input)))
return @input

As you can see, it's not the most lengthy piece of code ever written, but some parts are quite complex and require some explanation. I could have simplified some lines, but beyond a certain point you end up creating variables for the sake of it. I think it's mostly intelligible enough as it is.


Let's begin at the beginning. We start with the function declaration. It's a SQL Server user defined function, and it takes a string as an argument. It also returns a string, once it's been completely decoded. Then we define three variables. It can probably be done with less, but three was what I ended up with. These will store 2 character combinations. In the first instance these will be the hexadecimal parts of the character code, so the "20" in "%20" (space). During the process these will be converted to the characters they represent.

From here we plunge into a while loop. The loop condition is based on a charindex function, specifically, we're looking for the next instance of the "%" character. In a URL string this character identifies the hexadecimal character codes we're looking for. The function adjusts the input string as it goes along, so the first code is processed first, the string is updated and when the loop starts again, we're looking for the second code. Eventually there will be no more instances of "%" in the string and the loop will exit (when the result of the charindex function is 0).

Now we set @char equal to the 2 characters immediately following the current instance of "%". This is the code we're after. We deal with each character of the code separately. A 2-digit hexadecimal number can be converted to decimal by simply multiplying the first digit by 16 (as long as it's numeric to start with) and adding the second digit to it (again as long as it's numeric). This is what we do next. Firstly, is the first digit numeric? If so, we apply the formula above, if not we do some conversions.

This part is a bit more interesting. Instead of trying to convince SQL Server that "A" could be a number as well as a character, I cheated. The ASCII code for "A" is 65. "A" in hexadecimal represents the number 10. Therefore, the decimal value of the hexadecimal digits A-F is simply their ASCII code minus 55. So, if the digit we're currently looking at is not numeric, we apply this rule to get its actual value.

To illustrate, say for example we've been given the following character code: "%3F", which is the code for "?". Our routine looks for the "%", it's right at the start. We get the next two characters "3F". We look at the first character, it's numeric, so we multiply it by 16. This leaves us with 48 so far, and "F" to add on. "F" is not numeric, so we apply the other rules. The ASCII code for F is 70. We take 55 away from that to end up with 15 (the correct value for F). We add 15 to our 48 and we get 63. We use the chr function to convert this number to its ASCII equivalent and we get "?", just as we wanted.

The function works its way through the input string, applying the reasoning above to each encoded character. When it gets to the end of the string it exits the loop and returns the decoded string.


I'm not convinced that this is the best way to convert hexadecimal codes to ASCII characters, but it does work and in the absence of a better way I'm offering it up for use. Because of the way the routine works, it would also be possible to strip bits out of it and use it as a general purpose hex->dec converter. Reversing the process should also allow you to encode characters, although there will be a bit of code to write to stipulate which characters require conversion.

This code is the product of lack of sleep and some inexplicable lateral thinking. I hope it helps someone.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

GeneralMy vote of 5 Pin
PiyushVarma18-May-12 7:11
MemberPiyushVarma18-May-12 7:11 
GeneralProblem with UTF8 Pin
RichTeel25-May-07 16:10
MemberRichTeel25-May-07 16:10 
QuestionRe: Problem with UTF8 Pin
jaanek19-Oct-08 3:34
Memberjaanek19-Oct-08 3:34 
AnswerRe: Problem with UTF8 Pin
RichTeel19-Oct-08 8:15
MemberRichTeel19-Oct-08 8:15 
GeneralRe: Problem with UTF8 Pin
jaanek19-Oct-08 10:11
Memberjaanek19-Oct-08 10:11 
GeneralRe: Problem with UTF8 Pin
Quirpy14-Jan-15 4:32
MemberQuirpy14-Jan-15 4:32 
Generalnice... Pin
El_Guapo_885-Feb-07 9:07
MemberEl_Guapo_885-Feb-07 9:07 
GeneralSweet Pin
reshi9991-Feb-07 1:13
Memberreshi9991-Feb-07 1:13 
QuestionWhy? Pin
Alexandru Lungu25-Jan-07 12:52
professionalAlexandru Lungu25-Jan-07 12:52 
AnswerRe: Why? Pin
miies25-Jan-07 22:41
Membermiies25-Jan-07 22:41 
GeneralRe: Why? Pin
orinoco7726-Jan-07 0:06
Memberorinoco7726-Jan-07 0:06 
GeneralRe: Why? Pin
Jan Seda26-Jan-07 0:12
professionalJan Seda26-Jan-07 0:12 
GeneralRe: Why? Pin
miies26-Jan-07 1:50
Membermiies26-Jan-07 1:50 
GeneralRe: Why? Pin
orinoco7726-Jan-07 6:11
Memberorinoco7726-Jan-07 6:11 
GeneralRe: Why? Pin
Adam Byrne26-Jan-07 6:19
MemberAdam Byrne26-Jan-07 6:19 
GeneralRe: Why? Pin
orinoco7726-Jan-07 6:27
Memberorinoco7726-Jan-07 6:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.