SQL Server 网址解码

问题描述

我需要对存储 URL 编码文本的旧表运行查询.我需要在我的结果中解码此文本.我如何实现这一目标?

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?

推荐答案

尝试以下方法之一:

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

来自 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

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

这篇关于SQL Server 网址解码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！