SQL Server Url Decoding

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

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88926

Url Decode T-SQL function not translating out of ascii scope characters

I found this function that achieve exactly what I want :

ALTER FUNCTION [dbo].[UrlDecodeUTF8](@URL varchar(3072))
RETURNS varchar(3072)
AS
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 > 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, '+', ' ')

END

Is there any way to do HTML decode in SQL Server?

The following SQL function would work in your case or it would be a good starting point for you to extend it. However, please note the String manipulations in the Database [SQL Server] would be slower compared to the string manipulations in application layer.

GO

IF OBJECT_ID('dbo.MyHTMLDecode') IS NOT NULL BEGIN DROP FUNCTION dbo.MyHTMLDecode END

GO
CREATE FUNCTION dbo.MyHTMLDecode (@vcWhat VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @vcResult VARCHAR(MAX)
    DECLARE @siPos INT
        ,@vcEncoded VARCHAR(7)
        ,@siChar INT

    SET @vcResult = RTRIM(LTRIM(CAST(REPLACE(@vcWhat COLLATE Latin1_General_BIN, CHAR(0), '') AS VARCHAR(MAX))))

    SELECT @vcResult = REPLACE(REPLACE(@vcResult, ' ', ' '), ' ', ' ')

    IF @vcResult = ''
        RETURN @vcResult

    SELECT @siPos = PATINDEX('%&#[0-9][0-9][0-9];%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 6)
            ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, 3) AS INT)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
            ,@siPos = PATINDEX('%&#[0-9][0-9][0-9];%', @vcResult)
    END

    SELECT @siPos = PATINDEX('%&#[0-9][0-9][0-9][0-9];%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 7)
            ,@siChar = CAST(SUBSTRING(@vcEncoded, 3, 4) AS INT)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, NCHAR(@siChar))
            ,@siPos = PATINDEX('%&#[0-9][0-9][0-9][0-9];%', @vcResult)
    END

    SELECT @siPos = PATINDEX('%#[0-9][0-9][0-9][0-9]%', @vcResult)

    WHILE @siPos > 0
    BEGIN
        SELECT @vcEncoded = SUBSTRING(@vcResult, @siPos, 5)
            ,@vcResult = REPLACE(@vcResult, @vcEncoded, '')
            ,@siPos = PATINDEX('%#[0-9][0-9][0-9][0-9]%', @vcResult)
    END

    SELECT @vcResult = REPLACE(REPLACE(@vcResult, NCHAR(160), ' '), CHAR(160), ' ')

    SELECT @vcResult = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vcResult, '&', '&'), '"', '"'), '<', '<'), '>', '>'), '&amp;', '&')

    RETURN @vcResult
END

GO

Illustration:

  DECLARE @S VARCHAR(MAX)='CD&amp;amp;M Communications 
    auburndale oil &amp;amp; propane inc  
    C F La Fountaine #7561  
    Laramie County Fire District # 2  
    AmeriGas Propane LP #2250'

    SELECT dbo.MyHTMLDecode (@s)

OUTPUT:

CD&M Communications 
auburndale oil & propane inc  
C F La Fountaine   
Laramie County Fire District # 2  
AmeriGas Propane LP

Server.UrlEncode for SQL?

You are looking to perform Percent-Encoding/Decoding which is defined in Section 2.1 of RFC 3986, titled "Uniform Resource Identifier (URI): Generic Syntax".

You can use this section to determine how to percent-encode/decode URIs in T-SQL.

Specifically, you'll want to pay attention to all of Section 2: Characters with specific attention paid to Section 2.4: When to Encode or Decode.

The best way to do this, of course, is to perform this before/after you save/query the database. Relational databases in general aren't made for this kind of computational work.

If you use code, you might want to take a look at the Microsoft Web Protection Library on CodePlex, as it contains mechanisms for percent-encoding/decoding which are much more up-to-date in regards to taking into account vulnerabilities that can arise from poor encoding schemes.

urlencode with only built-in functions

select regexp_replace(encode('héllo there','hex'),'(..)',E'%\\1','g');

This doesn't leave the alphanumeric characters human-readable, though.

DECODE( ) function in SQL Server

You could use the 'CASE .. WHEN .. THEN .. ELSE .. END' syntax in SQL.

How to decode html encoded text in sql server? (or ms access!)

Alex K is 99.99% correct, however the conversion would fail if you had Named Codes like or £

So, here we perform a brute force replace, and then parse the string via XML

Example

Declare @S nvarchar(max) = 'sweet shop £'

Select @S = replace(@S,MapFrom,MapTo)
 From  ( values
        ('"','"'),('&','&'),(''',''''),('<','<'),('>','>'),(' ',' '),('¡','¡'),
        ('¢','¢'),('£','£'),('¤','¤'),('¥','¥'),('¦','¦'),('§','§'),('¨','¨'),
        ('©','©'),('ª','ª'),('«','«'),('¬','¬'),('®','®'),('¯','¯'),('°','°'),
        ('±','±'),('²','²'),('³','³'),('´','´'),('µ','µ'),('¶','¶'),('·','·'),
        ('¸','¸'),('¹','¹'),('º','º'),('»','»'),('¼','¼'),('½','½'),('¾','¾'),
        ('¿','¿'),('À','À'),('Á','Á'),('Â','Â'),('Ã','Ã'),('Ä','Ä'),('Å','Å'),
        ('Æ','Æ'),('Ç','Ç'),('È','È'),('É','É'),('Ê','Ê'),('Ë','Ë'),('Ì','Ì'),
        ('Í','Í'),('Î','Î'),('Ï','Ï'),('Ð','Ð'),('Ñ','Ñ'),('Ò','Ò'),('Ó','Ó'),
        ('Ô','Ô'),('Õ','Õ'),('Ö','Ö'),('×','×'),('Ø','Ø'),('Ù','Ù'),('Ú','Ú'),
        ('Û','Û'),('Ü','Ü'),('Ý','Ý'),('Þ','Þ'),('ß','ß'),('à','à'),('á','á'),
        ('&;','â'),('ã','ã'),('ä','ä'),('å','å'),('æ','æ'),('ç','ç'),('è','è'),
        ('é','é'),('ê','ê'),('ë','ë'),('ì','ì'),('í','í'),('î','î'),('ï','ï'),
        ('ð','ð'),('ñ','ñ'),('ò','ò'),('ó','ó'),('ô','ô'),('õ','õ'),('ö','ö'),
        ('÷','÷'),('ø','ø'),('ù','ù'),('ú','ú'),('û','û'),('ü','ü'),('ý','ý'),
        ('þ','þ'),('ÿ','ÿ'),('&','&'),('°','°'),('∞','∞'),('‰','‰'),('⋅','⋅'),
        ('±','±'),('†','†'),('—','—'),('¬','¬'),('µ','µ'),('€','€'),('£','£'),
        ('¥','¥'),('¢','¢'),('€','€'),('£','£'),('¥','¥'),('¢','¢')
       ) A (MapFrom,MapTo)

Select cast(cast(@S as xml) as nvarchar(max))

Returns

sweet shop £  -- added a space ( ) and a Pound (£) symbol to the original string

SQL Server Url Decoding