I need to run a query against a legacy table that stores URL encoded text. I need this text to be decoded in my results. How do I achieve this?
1
-
If you are using a programming language to run the query, it's probably easiest to URL encode the search query as well.Pekka– Pekka2010-09-30 17:33:37 +00:00Commented Sep 30, 2010 at 17:33
Add a comment
|
5 Answers
Try one of these:
CREATE FUNCTION dbo.UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
AS
BEGIN
DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072)
SET @count = Len(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count)
BEGIN
SET @c = substring(@url, @i, 1)
IF @c LIKE '[!%]' ESCAPE '!'
BEGIN
SET @cenc = substring(@url, @i + 1, 2)
SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@cenc, 1, 1) as int)
ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int)
END * 16 +
CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@cenc, 2, 1) as int)
ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int)
END)
SET @urlReturn = @urlReturn + @c
SET @i = @i + 2
END
ELSE
BEGIN
SET @urlReturn = @urlReturn + @c
END
SET @i = @i +1
END
RETURN @urlReturn
END
GO
from http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx
CREATE FUNCTION dbo.fnDeURL
(
@URL VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
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 > 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
END
1 Comment
Jordan Allan
Went with the first link. You my friend, are a life saver. Thanks!
If you need a solution that support non english characters (unicode), there's an excelent example on CodeProject:
CREATE FUNCTION [dbo].[UrlDecode] (
@URL NVARCHAR(4000) ) RETURNS NVARCHAR(4000) AS BEGIN
DECLARE @Position INT, @Base CHAR(16), @High TINYINT, @Low TINYINT, @Pattern CHAR(21)
DECLARE @Byte1Value INT, @SurrogateHign INT, @SurrogateLow INT
SELECT @Pattern = '%[%][0-9a-f][0-9a-f]%', @Position = PATINDEX(@Pattern, @URL)
WHILE @Position > 0
BEGIN
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = 16 * @High + @Low
IF @Byte1Value < 128 --1-byte UTF-8
SELECT @URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
@Position = PATINDEX(@Pattern, @URL)
ELSE IF @Byte1Value >= 192 AND @Byte1Value < 224 AND @Position > 0 --2-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,5) - 1)) * POWER(2,6),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
@URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
@Position = PATINDEX(@Pattern, @URL)
END
ELSE IF @Byte1Value >= 224 AND @Byte1Value < 240 AND @Position > 0 --3-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,4) - 1)) * POWER(2,12),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
@URL = STUFF(@URL, @Position, 3, NCHAR(@Byte1Value)),
@Position = PATINDEX(@Pattern, @URL)
END
ELSE IF @Byte1Value >= 240 AND @Position > 0 --4-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,3) - 1)) * POWER(2,18),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,12),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
@URL = STUFF(@URL, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @URL)
IF @Position > 0
BEGIN
SELECT @High = ASCII(UPPER(SUBSTRING(@URL, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@URL, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1))
--,@URL = STUFF(@URL, @Position, 3, cast(@Byte1Value as varchar))
--,@Position = PATINDEX(@Pattern, @URL)
SELECT @SurrogateHign = ((@Byte1Value - POWER(16,4)) & (POWER(2,20) - 1)) / POWER(2,10) + 13 * POWER(16,3) + 8 * POWER(16,2),
@SurrogateLow = ((@Byte1Value - POWER(16,4)) & (POWER(2,10) - 1)) + 13 * POWER(16,3) + 12 * POWER(16,2),
@URL = STUFF(@URL, @Position, 3, NCHAR(@SurrogateHign) + NCHAR(@SurrogateLow)),
@Position = PATINDEX(@Pattern, @URL)
END
END
END
RETURN REPLACE(@URL, '+', ' ') END
Comments
create function [dbo].[URLDecode](
@str nvarchar( max )
) returns nvarchar( max )
begin
if @str is null return null
declare @out nvarchar( max ) = N''
set @str = replace( @str, N'+', N'%20' )
while len(@str) > 0
begin
declare @i bigint = patindex( N'%[%][0-9a-fA-F][0-9a-fA-F]%', @str )
if @i = 0 break
set @out = @out + substring( @str, 0, @i ) + convert( nchar(2), convert( varbinary, '0x' + substring( @str, @i + 1, 2 ), 1 ) )
set @str = substring( @str, @i + 3, len(@str) )
end
return @out + @str
end
Comments
I would add some additional decoding. One error I came across is the return value was null. I also noticed that a few of the answers above replaced the '+' with a space.
ALTER FUNCTION UrlDecode(@url varchar(3072))
RETURNS varchar(3072)
AS BEGIN
DECLARE @count int,
@c char(1),
@cenc char(2),
@i int,
@urlReturn varchar(3072)
SET @count = Len(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i <= @count) BEGIN
SET @c = substring(@url, @i, 1)
IF @c LIKE '[!%]' ESCAPE '!' BEGIN
SET @cenc = substring(@url, @i + 1, 2)
SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@cenc, 1, 1) as int)
ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int)
END * 16 +
CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]'
THEN CAST(SUBSTRING(@cenc, 2, 1) as int)
ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int)
END)
SET @urlReturn = @urlReturn + @c
SET @i = @i + 2
END ELSE BEGIN
SET @urlReturn = @urlReturn + @c
END
SET @i = @i +1
END
IF @urlReturn is null BEGIN
set @urlReturn = ''
END ELSE BEGIN
set @urlReturn = REPLACE(@urlReturn, '+', ' ')
END
RETURN @urlReturn
END
GO
Comments
I had to improve performence for decoding large amounts of text (several Kb):
CREATE FUNCTION [dbo].[fn_url_decode]
(@encoded_text as nvarchar(max))
/*****************************************************************************************************************************
* Autor: Nuno Sousa
* Data criação: 2025-04-18
*
* Descrição: Faz o "URL decode" da string passada em @encoded_text
*
* PARÂMETROS
*
* @encoded_text
* texto que está encoded e que será decoded por esta função
*
*****************************************************************************************************************************/
RETURNS nvarchar(max)
AS BEGIN
/**********************************
DEBUG
declare @encoded_text nvarchar(max) = '%C3%81%20meu%20nome%20%C3%A1%C3%A9'
**********************************/
declare @decoded_text nvarchar(max) = ''
declare @decoded_char nvarchar(2) = ''
DECLARE @Position INT
,@Base CHAR(16)
,@High TINYINT
,@Low TINYINT
,@Pattern CHAR(21)
DECLARE @Byte1Value INT
,@SurrogateHign INT
,@SurrogateLow INT
SELECT @Pattern = '%[%][0-9a-f][0-9a-f]%'
,@Position = PATINDEX(@Pattern, @encoded_text)
WHILE @Position > 0
BEGIN
if @Position > 1
begin
set @decoded_text = @decoded_text + left(@encoded_text,@Position - 1)
set @encoded_text = substring(@encoded_text, @Position, len(@encoded_text))
set @Position = 1
end
set @decoded_char = ''
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = 16 * @High + @Low
IF @Byte1Value < 128 --1-byte UTF-8
begin
SELECT @decoded_char = NCHAR(@Byte1Value)
,@encoded_text = substring(@encoded_text, 4, len(@encoded_text))
,@Position = PATINDEX(@Pattern, @encoded_text)
end
ELSE IF @Byte1Value >= 192 AND @Byte1Value < 224 AND @Position > 0 --2-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,5) - 1)) * POWER(2,6),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
@decoded_char = NCHAR(@Byte1Value),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
END
ELSE IF @Byte1Value >= 224 AND @Byte1Value < 240 AND @Position > 0 --3-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,4) - 1)) * POWER(2,12),
@encoded_text = STUFF(@encoded_text, @Position, 3, ''),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
@decoded_char = NCHAR(@Byte1Value),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)),
@decoded_char = NCHAR(@Byte1Value),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
END
ELSE IF @Byte1Value >= 240 AND @Position > 0 --4-byte UTF-8
BEGIN
SELECT @Byte1Value = (@Byte1Value & (POWER(2,3) - 1)) * POWER(2,18),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,12),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1)) * POWER(2,6),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
IF @Position > 0
BEGIN
SELECT @High = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 1, 1))) - 48,
@Low = ASCII(UPPER(SUBSTRING(@encoded_text, @Position + 2, 1))) - 48,
@High = @High / 17 * 10 + @High % 17,
@Low = @Low / 17 * 10 + @Low % 17,
@Byte1Value = @Byte1Value + ((16 * @High + @Low) & (POWER(2,6) - 1))
--,@encoded_text = STUFF(@encoded_text, @Position, 3, cast(@Byte1Value as varchar))
--,@Position = PATINDEX(@Pattern, @encoded_text)
SELECT @SurrogateHign = ((@Byte1Value - POWER(16,4)) & (POWER(2,20) - 1)) / POWER(2,10) + 13 * POWER(16,3) + 8 * POWER(16,2),
@SurrogateLow = ((@Byte1Value - POWER(16,4)) & (POWER(2,10) - 1)) + 13 * POWER(16,3) + 12 * POWER(16,2),
@decoded_char = NCHAR(@SurrogateHign) + NCHAR(@SurrogateLow),
@encoded_text = substring(@encoded_text, 4, len(@encoded_text)),
@Position = PATINDEX(@Pattern, @encoded_text)
END /* IF @Position > 0 */
END /* IF @Byte1Value */
set @decoded_text = @decoded_text + @decoded_char
END /* WHILE @Position > 0 */
set @decoded_text = @decoded_text + @encoded_text
--select REPLACE(@decoded_text, '+', ' '),@num_ciclos
RETURN REPLACE(@decoded_text, '+', ' ')
END /* CREATE FUNCTION [dbo].[fn_url_decode] */
1 Comment
Jeremy Caney
Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?