Title: Url Decode T-SQL function not translating out of ascii scope characters
Tags: sql-server;tsql;ascii;urldecode;non-ascii-characters
Question: I'm using this function to decode url encoded string :
ALTER FUNCTION [dbo].[UrlDecode](@url varchar(3072))
RETURNS varchar(3072)
BEGIN
DECLARE @Position INT,
@Base CHAR(16),
@High TINYINT,
@Low TINYINT,
@Pattern CHAR(21)
SELECT  @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@URL = REPLACE(@URL, '+', ' '),
@Position = PATINDEX(@Pattern, @URL)
WHILE @Position &gt; 0
SELECT  @High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base COLLATE Latin1_General_CI_AS),
@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base COLLATE Latin1_General_CI_AS),
@URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
@Position = PATINDEX(@Pattern, @URL)
RETURN  @URL +
This works fine until it reaches special out of ascii scope characters. Example : Wil+SG+1 will return Wil SG 1 which is OK. While Gen%C3%A8ve+11 returns GenÃ¨ve 11 which is not what I expect (Genève 11 is the expected result in this case).
another example :
select 'Gen%C3%A8ve+2+D%C3%A9p%C3%B4t', dbo.UrlDecode('Gen%C3%A8ve+2+D%C3%A9p%C3%B4t')
returns :
Gen%C3%A8ve+2+D%C3%A9p%C3%B4t   GenÃ¨ve 2 DÃ©pÃ´t
I've tried using NCHAR instead of CHAR but the result is the same. Do you know what I can do to support these extended ascii chars ?
Comment: @RBarryYoung No it's utf8 encoded so the correct result is è not Ã

Comment: @RBarryYoung I just quote wikipedia http://en.wikipedia.org/wiki/Percent-encoding : "(For a non-ASCII character, it is typically converted to its byte sequence in UTF-8, and then each byte value is represented as above.)" ... Yes UTF-8

Comment: Yes you are right not alway but in this case I have to decode the utf-8 to translate it correctly. Look at my own answer to see what I had to do to make it work.

Comment: I think this link may help -> http://stackoverflow.com/questions/3585138/how-can-i-convert-a-string-to-a-string-encoded-in-utf-8-and-vice-versa

Comment: Your routine does not compile because `RETURN @URL + ` is not a valid SQL statement.  What's supposed to be on that line?

Comment: Wait, ***why*** would you expect `Gen%C3%A8ve+11` to return `Genève 11`? Isn't UC+00C3 = "Ã"?  That would make `GenÃ¨ve 11` the correct answer.  And `Genève 11` doesn't even have the correct length?!?

Comment: Actually it's not UTF-8 encoded at all, nor does your function decode UTF-8 (which is a variable-width encoding).  The strings that you are displaying are what's called ***Percent Encoded*** (see http://en.wikipedia.org/wiki/Percent-encoding), and what this algorithm does is correct Percent Decoding.  It doesn't do anything even remotely like valid UTF-8 Decoding.

Comment: @Arno2501 Note the word "*typically*", which also means "*not always*".  The problem here is that you've just assumed that SQL Server uses UTF-8, but it doesn't.  SQL Server uses either ASCII or UCS-2.  When you are "Decoding" you have to decode into something that the decoding host/target uses or understands.  Otherwise it's not "decoding" but rather "re-coding".

Here is the accepted answer: I found this function that achieve exactly what I want :
ALTER FUNCTION [dbo].[UrlDecodeUTF8](@URL varchar(3072))
RETURNS varchar(3072)
BEGIN
DECLARE @Position INT,
@Base CHAR(16),
@Code INT,
@Pattern CHAR(21)
SELECT @URL = REPLACE(@URL, '%c3', '')
SELECT  @Base = '0123456789abcdef',
@Pattern = '%[%][0-9a-f][0-9a-f]%',
@Position = PATINDEX(@Pattern, @URL)
WHILE @Position &gt; 0
SELECT @Code = Cast(CONVERT(varbinary(4), '0x' + SUBSTRING(@URL, @Position + 1, 2), 1) As int),
@URL = STUFF(@URL, @Position, 3, NCHAR(@Code + 64)),
@Position = PATINDEX(@Pattern, @URL)
RETURN REPLACE(@URL, '+', ' ')

Here is another answer: URLs are encoded in UTF-8. What your function does is simply replace the hex codes of the UTF-8 representation of the URL with the characters matching the hex codes.
What you really need is a function to replace URL-encoded UTF-8 to MSSQL UCS-2, as posted in this answer on Social.MSDN.
Comment for this answer: You are absolutely right thanks for your answer but I realized that with mehdi's comment. thanks anyway.

Here is another answer: I suspect you'll need to play around with the collation, replacing UTF codes with some ascii equivalent. Here's an example I had in my code library:
REPLACE(CHAR(228) COLLATE Latin1_General_BIN, CHAR(196), 'Y')
Comment for this answer: thanks for your answer but I realized with mehdi's comment that it is utf8 encoded so I have to decode utf8

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 8, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:02/26/13', 'FavoriteCount': 0.0, 'PostTypeId': 1, 'Tags': '<sql-server><tsql><ascii><urldecode><non-ascii-characters>', 'Title': 'Url Decode T-SQL function not translating out of ascii scope characters', 'ViewCount': 6122, 'id': 15092770, 'user_AboutMe': "<p>I'm a sotware engineer building software for the web for more than 10 years.</p>\n", 'user_AccountId': 390575.0, 'user_DisplayName': 'Arno 2501', 'user_Reputation': 8413.0, 'user_Views': 281.0, 'user_id': 750725.0}}