Décodage d'URL SQL Server

J'ai besoin d'exécuter une requête sur un héritage de la table qui stocke l'URL du texte codé. J'ai besoin de ce texte pour être décodé dans mes résultats. Comment puis-je y parvenir?

source d'informationauteur Jordan Allan

  1. 20

    Essayer un de ces:

    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

    de 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

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

  2. 0
    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