不正确的 SQL 语法 - 字符串后的未关闭引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3422306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incorrect SQL Syntax - Unclosed quotation mark after the character string
提问by user412814
Now i have string a = "Unclosed quotation mark after the character string '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'. Incorrect syntax near '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'."for filter
现在我有a = "Unclosed quotation mark after the character string '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'. Incorrect syntax near '%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC'."过滤器的字符串
I'm using replace ("'","''") replace ("%","[%]") replace ("[","[[]")
我正在使用替换 ("'","''") 替换 ("%","[%]") 替换 ("[","[[]")
and I have as a result for string strSQL =
我的结果是字符串 strSQL =
select * from studiologs
where [Message]
like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''. Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''.%'
but result always is null please help me replace this string for filter
但结果总是为空，请帮我替换这个字符串作为过滤器
thanks all
谢谢大家
回答by Sachin Shanbhag
I think you have missed one more quotation mark at end of query -
我想你在查询结束时又错过了一个引号 -
select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''%'
or remove that last quotation mark as well, if your string does not have '
或删除最后一个引号，如果您的字符串没有 '
select * from studiologs where [Message] like '%Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC%'
Depending on what you are searching exactly
取决于您正在搜索的内容
回答by Tom 'Blue' Piddock
Best thing I would do here is to transfer your SQL query into a procedure, that way the string you give it wont need filtering as the punctuation in the string will notaffect the syntax of the query.
我在这里做的最好的事情是将您的 SQL 查询转移到一个过程中，这样您提供的字符串就不需要过滤，因为字符串中的标点符号不会影响查询的语法。
So something like this:
所以像这样：
USE MYDATABASE
CREATE PROC GET_STUDIO_LOGS
@INPUT_STRING AS NVARCHAR(1024)
AS
BEGIN
SELECT * FROM STUDIOLOGS WHERE [Message] LIKE '%' + @INPUT_STRING + '%'
END
EXEC GET_STUDIO_LOGS 'Unclosed quotation mark after the character string ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''. Incorrect syntax near ''%Bamboo.Widgets.RequestApproval.CollectTask ORDER BY Date DESC''.'
If you use a program to submit the SQL then you can submit the string parameter as it is without any change in punctuation. Doing it natively in SQL you just add another '(quotemark) to each quotemark that is meant to be part of the string.
如果您使用程序提交 SQL，那么您可以按原样提交字符串参数，标点符号没有任何变化。在 SQL 中进行本机操作，您只需将另一个'（引号）添加到每个要成为字符串一部分的引号中。
If you are trying to escape the % marks you can set an escape character first:
如果您试图转义 % 标记，您可以先设置一个转义字符：
SET ESCAPE '\';
SELECT '\%abc' FROM Table
Try that out, tell me how it goes.
试试看，告诉我它是怎么回事。
