动态SQL查询和特殊字符问题 [英] Dynamic SQL Query and problems with special characters
本文介绍了动态SQL查询和特殊字符问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
现在，我正在运行查询以查找文档中的动态词并将其替换为链接。它工作正常，但是当有一些特殊字符时返回 null 。目前，我对动态词包含和号'&'
Right now I am running a query to find and replace a dynamic word within a document into a link. It works just fine but returns null when there are some special characters. Currently I am having issues with the dynamic word contains an ampersand '&'
的问题是这里的一部分：
Here is part of the content:
<p>STEPS:</p>
<p>
<p>Please refer to <|Cease & Desist|> policy.</p>
返回 NULL c $ c>'Case& amp; Desist'
It is returning a NULL for 'Cease & Desist'
下面是我正在使用的代码，我需要的是即使它包含&符也可以返回整个动态单词
Below is the code I am using, what I need is for it to return the entire dynamic word even if it contains the ampersand.
SET NOCOUNT ON;
DECLARE @MyTable TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
PolicyName NVARCHAR(200) NULL,
DocumentID int not null,
OldContent NVARCHAR(MAX) NOT NULL,
NewContent NVARCHAR(MAX) NULL
);
INSERT INTO @MyTable (documentid,OldContent)
select documentid, html from IPACS_Document where PATINDEX('%Please refer to <|%', html) > 0 and PATINDEX('%|> Policy%', html) > 0 and documentid = 147;
--select html, PATINDEX('%Please refer to <|%', html), PATINDEX('%|> Policy%', html) from IPACS_Document where PATINDEX('%Please refer to <|%', html) > 0;
WITH UpdateCTE
AS
(
SELECT b.PolicyName, b.[text], b.NewContent,STUFF(b.InnerText,b.StartIndex-5,b.EndIndex-b.StartIndex+10,'<a href="~/Document/Details/'+ CAST(d.[documentid] as VARCHAR(200))+'">'+b.[Text]+'</a>') AS ChangedText
FROM
(
SELECT a.*,SUBSTRING(a.InnerText,a.StartIndex,a.EndIndex-a.StartIndex) AS [Text]
FROM
(
SELECT PATINDEX('%Please refer to <|%',t.OldContent)+21 AS StartIndex,
PATINDEX('%|> Policy%',t.OldContent) AS EndIndex,
t.OldContent AS InnerText,
t.NewContent,
t.PolicyName
FROM @MyTable t
) a
) b
inner join IPACS_Document d on d.filename like '%' + b.[text] + '%'
where d.categoryid = 3
)
--select * from UpdateCTE
UPDATE UpdateCTE
SET NewContent = ChangedText, PolicyName = [text];
SELECT *
FROM @MyTable x;
在上面的查询中，最内部的查询查询'a' InnerText列名[text] 是包含动态单词的内容。这作为 [文本] 传递到表‘b’。最终，在 @Mytable 中将动态词放入 PolicyName 。
In the above query the most inner query query 'a' InnerText column name [text] is what contains the dynamic word. This is passed to table 'b' as [text]. Eventually in @Mytable the dynamic word is put into PolicyName.
当动态词包含&符号或特殊字符时，该如何工作？
How can I allow this to work when the dynamic word contains an ampersand or special character?
推荐答案
SET NOCOUNT ON;
DECLARE @MyTable TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
PolicyName NVARCHAR(200) NULL,
DocumentID int not null,
OldContent NVARCHAR(MAX) NOT NULL,
NewContent NVARCHAR(MAX) NULL
);
INSERT INTO @MyTable (PolicyName,OldContent,DocumentID)
VALUES (NULL,N'<p>STEPS:</p>
<p>
<p>Please refer to <|Cease & Desist|> policy.</p>',123);
;WITH UpdateCTE
AS
(
SELECT b.PolicyName, b.[text], b.NewContent,STUFF(b.InnerText,b.StartIndex-5,b.EndIndex-b.StartIndex+10,'<a href="~/Document/Details/'+ CAST(b.[documentid] as VARCHAR(200))+'">'+b.[Text]+'</a>') AS ChangedText
FROM
(
SELECT a.*,SUBSTRING(a.InnerText,a.StartIndex,a.EndIndex-a.StartIndex) AS [Text]
FROM
(
SELECT PATINDEX('%Please refer to <|%',t.OldContent)+21 AS StartIndex,
PATINDEX('%|> Policy%',t.OldContent) AS EndIndex,
t.OldContent AS InnerText,
t.NewContent,
t.PolicyName,
t.DocumentID
FROM @MyTable t
) a
) b
--inner join IPACS_Document d on d.filename like '%' + b.[text] + '%'
--where d.categoryid = 3
)
--select * from UpdateCTE
UPDATE UpdateCTE
SET NewContent = ChangedText, PolicyName = [text];
SELECT * FROM @MyTable x;
结果：
ID PolicyName DocumentID OldContent NewContent
-- ------------------- ----------- ----------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Cease & Desist 123 <p>STEPS:</p> <p> <p>Please refer to <|Cease & Desist|> policy.</p> <p>STEPS:</p> <p> <p>Please refer to <a href="~/Document/Details/123">Cease & Desist</a> policy.</p>
这篇关于动态SQL查询和特殊字符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
