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] */