如何转义 SQL LIKE 用户输入中的特殊字符？
采取以下示例（SQL Server 2008 - 可能适用于更多）。您必须将 @query 想象成某个参数，其来源是用户输入：
DECLARE @query varchar(100)
SET @query = 'less than 1% fat'
CREATE TABLE X ([A] VARCHAR(100))
INSERT X VALUES ('less than 1% fat')
INSERT X VALUES ('less than 1% of doctors recommend this - it''s full of fat!')
SELECT * FROM X WHERE A LIKE '%' + @query + '%'
DROP TABLE X
查询声明 “脂肪含量低于 1%”，但实际上我们得到的比我们想要的要多：
less than 1% fat
less than 1% of doctors recommend this - it's full of fat!
为了获得所需的行为，我将 @query 更改为 '小于 1[%] fat' - 然后仅返回第一个结果。
是否有一种标准方法可以为使用 LIKE 的子句准备字符串，或者我必须自己编写字符串？
Take the following example (SQL Server 2008 - might work with more). You'll have to imagine @query being some parameter whose source is user input:
DECLARE @query varchar(100)
SET @query = 'less than 1% fat'
CREATE TABLE X ([A] VARCHAR(100))
INSERT X VALUES ('less than 1% fat')
INSERT X VALUES ('less than 1% of doctors recommend this - it''s full of fat!')
SELECT * FROM X WHERE A LIKE '%' + @query + '%'
DROP TABLE X
The query states 'less than 1% fat', but we actually get more than we wanted:
less than 1% fat
less than 1% of doctors recommend this - it's full of fat!
To get the required behaviour, I change @query to 'less than 1[%] fat' - then only the first result is returned.
Is there a standard way to prepare strings for clauses which use LIKEs, or do I have to roll my own?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以使用自由文本搜索和 CONTAINS 或FREETEXT 谓词。带有前导通配符的 LIKE 会忽略索引并导致全表扫描，而全文搜索则使用现有的自由文本索引来加速搜索。您必须先配置自由文本索引，然后才能使用它。
如果您想坚持使用 LIKE，最好的解决方案是转义客户端代码中的字符串。 T-SQL 提供非常有限的字符串操作功能，并且 REPLACE 函数不提供甚至不接受通配符。您必须嵌套多个 REPLACE 语句才能说明 LIKE 使用的所有通配符。
如果您的搜索字符串可能包含 [ 或 ] 字符，您可以将其与 ESCAPE 子句结合使用，并使用 §、¶ 或 ¤ 等罕见字符作为转义字符。
Instead of using LIKE you can use free-text search and the CONTAINS or FREETEXT predicates. LIKE with a leading wildcard ignores indexes and results in a full table scan while the full-text searches use existing free-text indexes to speed up the search. You will have to configure free text indexing before you can use it.
If you want to stick with LIKE the best solution would be to escape the string in your client code. T-SQL provides very limited string manipulation functionality and the REPLACE function doesn't even accept wildcards. You would have to nest multiple REPLACE statements to account for all wildcards used by LIKE.
You can combine that with the ESCAPE clause and use a rare character like §, ¶ or ¤ as an escape character if your search string may contain the [ or ] characters.
SQL Server允许您指定转义字符，然后您就可以转义使用查询字符串之前。
我链接的 MSDN 页面的示例：
SQL Server allows you to specify an escape character, then you can just escape the query string before using it.
Sample from the MSDN page I linked: