SQL Server:基于字符串输入中的 HTML 名称进行 HTML 解码 [英] SQL Server: HTML Decode based on the HTML names in a String input

问题描述

我正在尝试转换 HTML 名称，例如 &使用下面的 SQL 将 " 等转换为它们的等效 CHAR 值.我在 SQL Server 2012 中对此进行了测试.

I am trying to convert the HTML names like & " etc to their equivalent CHAR values using the SQL below. I was testing this in SQL Server 2012.

测试 1(这很好用):

Test 1 (This works fine):

GO
DECLARE @inputString VARCHAR(MAX)= '&testString&'
DECLARE @codePos INT, @codeEncoded VARCHAR(7), @startIndex INT, @resultString varchar(max)
SET @resultString = LTRIM(RTRIM(@inputString))
SELECT @startIndex = PATINDEX('%&%', @resultString)
WHILE @startIndex > 0 
BEGIN
    SELECT @resultString = REPLACE(@resultString, '&', '&'), @startIndex=PATINDEX('%&%', @resultString)
END

PRINT @resultString
Go

输出:

&testString&

测试 2(这不起作用):由于上述工作，我试图扩展它来处理更多的字符如下:

Test 2 (this isn't worked): Since the above worked, I have tried to extend this to deal with more characters as following:

DECLARE @htmlNames TABLE (ID INT IDENTITY(1,1), asciiDecimal INT, htmlName varchar(50))
INSERT INTO @htmlNames
VALUES (34,'"'),(38,'&'),(60,'<'),(62,'>'),(160,' '),(161,'¡'),(162,'¢')
-- I would load the full list of HTML names into this TABLE varaible, but removed for testing purposes
DECLARE @inputString VARCHAR(MAX)= '&testString&'
DECLARE @count INT = 0
DECLARE @id INT = 1
DECLARE @charCode INT, @htmlName VARCHAR(30)
DECLARE @codePos INT, @codeEncoded VARCHAR(7), @startIndex INT
        , @resultString varchar(max)
SELECT @count=COUNT(*) FROM @htmlNames

WHILE @id <=@count
BEGIN
    SELECT @charCode = asciiDecimal, @htmlname = htmlName
    FROM @htmlNames
    WHERE ID = @id

        SET @resultString = LTRIM(RTRIM(@inputString))
        SELECT @startIndex = PATINDEX('%' + @htmlName + '%', @resultString)
        While @startIndex > 0 
        BEGIN
            --PRINT @resultString + '|'  + @htmlName + '|' + NCHAR(@charCode)
            SELECT @resultString = REPLACE(@resultString, @htmlName, NCHAR(@charCode))
            SET @startIndex=PATINDEX('%' + @htmlName + '%', @resultString)
        END
        SET @id=@id + 1
END

PRINT @resultString

GO

输出:

&testString&

我不知道哪里出错了?任何帮助将非常感激.

I cannot figure out where I'm going wrong? Any help would be much appreciated.

我不想将字符串值加载到应用程序层然后应用 HTMLDecode 并保存回数据库.

I am not interested to load the string values into application layer and then apply HTMLDecode and save back to the database.

这行 SET @resultString = LTRIM(RTRIM(@inputString)) 在 WHILE 中，所以我用 @inputString 覆盖了结果.谢谢你，YanireRomero.

This line SET @resultString = LTRIM(RTRIM(@inputString)) was inside the WHILE so I was overwriting the result with @inputString. Thank you, YanireRomero.

我也喜欢@RichardDeeming 的解决方案，但在这种情况下它不适合我的需求.

I like @RichardDeeming's solution too, but it didn't suit my needs in this case.

推荐答案

这是一个不需要循环的更简单的解决方案:

Here's a simpler solution that doesn't need a loop:

DECLARE @htmlNames TABLE 
(
    ID INT IDENTITY(1,1), 
    asciiDecimal INT, 
    htmlName varchar(50)
);

INSERT INTO @htmlNames 
VALUES 
    (34,'"'),
    (38,'&'),
    (60,'<'),
    (62,'>'),
    (160,' '),
    (161,'¡'),
    (162,'¢')
;

DECLARE @inputString varchar(max)= '&test"<String>"&';
DECLARE @resultString varchar(max) = @inputString;

-- Simple HTML-decode:
SELECT
    @resultString = Replace(@resultString COLLATE Latin1_General_CS_AS, htmlName, NCHAR(asciiDecimal))
FROM
    @htmlNames
;

SELECT @resultString;
-- Output: &test&quot;<String>"&


-- Multiple HTML-decode:
SET @resultString = @inputString;

DECLARE @temp varchar(max) = '';
WHILE @resultString != @temp
BEGIN
    SET @temp = @resultString;

    SELECT
        @resultString = Replace(@resultString COLLATE Latin1_General_CS_AS, htmlName, NCHAR(asciiDecimal))
    FROM
        @htmlNames
    ;
END;

SELECT @resultString;
-- Output: &test"<String>"&

<小时>

按照@tomasofen 的建议更改为 NCHAR，并按照建议向 REPLACE 函数添加了区分大小写的排序规则来自@TechyGypo.

---

Changed to NCHAR, as suggested by @tomasofen, and added a case-sensitive collation to the REPLACE function, as suggested by @TechyGypo.

这篇关于SQL Server:基于字符串输入中的 HTML 名称进行 HTML 解码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！