如何在 SQL Server 中搜索带撇号的名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6509159/
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
How do I search for names with apostrophe in SQL Server?
提问by Ontonomo
SELECT *
FROM Header
WHERE (userID LIKE [%'%])
回答by Alex K.
Double them to escape;
加倍逃跑；
SELECT *
FROM Header
WHERE userID LIKE '%''%'
回答by codingbadger
SELECT *
FROM Header WHERE (userID LIKE '%''%')
回答by CatchingMonkey
SELECT * FROM Header WHERE userID LIKE '%' + CHAR(39) + '%'
回答by sanpat
SELECT * FROM TableName WHERE CHARINDEX('''',ColumnName) > 0
When you have column with large amount of nvarchar data and millions of records, general 'LIKE' kind of search using percentage symbol will degrade the performance of the SQL operation.
当您拥有包含大量 nvarchar 数据和数百万条记录的列时，使用百分比符号的一般“LIKE”类搜索会降低 SQL 操作的性能。
While CHARINDEX inbuilt TSQL function is much more faster and there won't be any performance loss.
而 CHARINDEX 内置的 TSQL 函数要快得多，而且不会有任何性能损失。
Reference SOpost for comparative view.
参考SO帖子进行比较。
回答by Adriano Carneiro
That's:
那是：
SELECT * FROM Header
WHERE (userID LIKE '%''%')
回答by JohnD
select * from Header where userID like '%''%'
Hope this helps.
希望这可以帮助。
回答by Siddanagouda Biradar
Compare Names containing apostrophe in DB through Java code
通过Java代码比较DB中包含撇号的名称
String sql="select lastname from employee where FirstName like '%"+firstName.trim().toLowerCase().replaceAll("'", "''")+"%'"
statement = conn.createStatement();
rs=statement.executeQuery(Sql);
iterate the results.
迭代结果。
回答by ailia
First of all my Searchquery value is from a user's input. I have tried all the answers on this one and all the results Google have given me, 90% of the answers says put '%''%'and the other 10% says a more complicated answers.
首先，我的搜索查询值来自用户的输入。我已经尝试了这个答案的所有答案以及谷歌给我的所有结果，90% 的答案说 put '%''%'，另外 10% 说更复杂的答案。
For some reason all of those did not work for me.
出于某种原因，所有这些都不适合我。
How ever I remembered that in MySQL (phpmyadmin) there is this built in search function so I tried it just to see how MySQL handles a search with an apostrophe, turns out MySQL just escaping apostrophe with a backslash LIKE '%\'%'so why just I replace apostrophe with a \'in every user's query.
我怎么记得在 MySQL (phpmyadmin) 中有这个内置的搜索功能，所以我尝试了它只是为了看看 MySQL 如何处理带撇号的搜索，结果 MySQL 只是用反斜杠转义撇号，LIKE '%\'%'所以为什么我只用撇号替换撇号\'在每个用户的查询中。
This is what I come up with:
这是我想出的：
if(!empty($user_search)) {
$r_user_search = str_ireplace("'","\'","$user_search");
$find_it = "SELECT * FROM table WHERE column LIKE '%$r_user_search%'";
$results = $pdo->prepare($find_it);
$results->execute();
This solves my problem. Also please correct me if this is still has security issues.
这解决了我的问题。 如果这仍然存在安全问题，请纠正我。
回答by Guffa
Brackets are used around identifiers, so your code will look for the field %'%in the Headertable. You want to use a string insteaed. To put an apostrophe in a string literal you use double apostrophes.
括号标识周围使用，所以你的代码会寻找该领域%'%的Header表。您想使用字符串代替。要将撇号放入字符串文字中，请使用双撇号。
SELECT *
FROM Header WHERE userID LIKE '%''%'
