带有特殊字符的 Sql 查询 - 如何处理？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4875015/
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
Sql query with special characters - how to handle?
提问by jestges
I've few emp names like
我有几个像
- john,1
- devil's
- corn
- 约翰，1
- 魔鬼的
- 玉米
something like this
像这样的东西
Now when i'm searching for these names I'm using
现在，当我搜索这些名字时，我正在使用
select * from emp where empname like ('john,1,devil's,corn')
But I'm not getting expected values also I'm getting error because emp name contains special charecters like , and '.
但是我没有得到预期值，我也收到了错误，因为 emp 名称包含特殊字符，如 , 和 '.
Can someone help me out how to solve this?
有人可以帮我解决这个问题吗？
回答by gbn
This assumes you have 3 discrete names in your example string
这假设您的示例字符串中有 3 个离散名称
Exact match. you need to double up quotes.
完全符合。你需要加倍引号。
select * from emp where
empname IN ('john,1' , 'devil''s', 'corn')
You can't LIKE/IN in SQL Server too.
你也不能在 SQL Server 中 LIKE/IN。
select * from emp where
empname like '%john,1%'
OR
empname like '%devil''s%'
OR
empname like '%corn%'
回答by Sparky
For most versions of SQL, you need to escape the single quote, for example.
例如，对于大多数版本的 SQL，您需要对单引号进行转义。
select * from emp where empname like ('john,1,devil''s,corn')
Also, the above example is looking for a very specific string value, you need to include * or ? as wildcard characters, so to look for all empname's like devil's, use
此外，上面的示例正在寻找一个非常具体的字符串值，您需要包含 * 或 ? 作为通配符，所以要查找所有像恶魔一样的 empname，请使用
select * from emp where empname like '%devil''s%'
Another example
另一个例子
select * from emp where empname like 'john,1%'
回答by Ratinho
if you use mysql:
如果您使用 mysql：
select * from emp where empname like ('john,1','devil\'s','corn')
回答by Sachin Shanbhag
select * from emp where empname like ('john\,1','devil\'s','corn\'') escape '\'
use keyword escapeto mention escape character for the query.
使用关键字转义来提及查询的转义字符。
回答by rajah9
If you are looking for an empname of "devil's" then I agree with the use of the escape character that Sachin and Ratinho used.
如果您正在寻找“devil's”的 empname，那么我同意使用 Sachin 和 Ratinho 使用的转义字符。
However, the likeclause is used for something that is like another. For example, if you are looking for something that starts with "devil's" then you might use
但是，该like子句用于与另一个相似的事物。例如，如果您正在寻找以“devil's”开头的东西，那么您可以使用
select * from emp where empname like ('devil\'s%')
which would match
哪个会匹配
devil's peak
devil's beard
devil's
but not
但不是
Devil's peak
deviled eggs
devil
If you want to match an empname with "devil's" in the middle, then you might use
如果您想将一个 empname 与中间的“devil's”相匹配，那么您可以使用
select * from emp where empname like ('%devil\'s%')
