如何在 SQL Server 中搜索带撇号的名称？

发布于 2024-11-17 16:33:14 字数 74 浏览 3 评论 0原文

SELECT *
  FROM Header
 WHERE (userID LIKE [%'%])

原文

SELECT *
  FROM Header
 WHERE (userID LIKE [%'%])

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟花肆意 2024-11-24 16:33:14

加倍他们以逃脱；

SELECT *
  FROM Header
 WHERE userID LIKE '%''%'

Double them to escape;

SELECT *
  FROM Header
 WHERE userID LIKE '%''%'

回复收藏 0 原文

多谢你的绝情让我学会死心 2024-11-24 16:33:14

SELECT     *
FROM Header WHERE (userID LIKE '%''%')

SELECT     *
FROM Header WHERE (userID LIKE '%''%')

回复收藏 0 原文

一张白纸 2024-11-24 16:33:14

SELECT *   FROM Header  WHERE userID LIKE '%' + CHAR(39) + '%'

SELECT *   FROM Header  WHERE userID LIKE '%' + CHAR(39) + '%'

回复收藏 0 原文

鸵鸟症 2024-11-24 16:33:14

SELECT * FROM TableName WHERE CHARINDEX('''',ColumnName) > 0

当您的列包含大量 nvarchar 数据和数百万条记录时，使用百分比符号的常规“LIKE”类型搜索会降低 SQL 操作的性能。

而CHARINDEX内置的TSQL函数速度更快，并且不会有任何性能损失。

请参考 SO 帖子进行比较。

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.

While CHARINDEX inbuilt TSQL function is much more faster and there won't be any performance loss.

Reference SO post for comparative view.

回复收藏 0 原文

遗心遗梦遗幸福 2024-11-24 16:33:14

那是：

SELECT * FROM Header 
WHERE (userID LIKE '%''%')

That's:

SELECT * FROM Header 
WHERE (userID LIKE '%''%')

回复收藏 0 原文

此生挚爱伱 2024-11-24 16:33:14

select * from Header where userID like '%''%'

希望这有帮助。

select * from Header where userID like '%''%'

Hope this helps.

回复收藏 0 原文

才能让你更想念 2024-11-24 16:33:14

首先，我的搜索查询值来自用户的输入。
我已经尝试了这个问题的所有答案以及 Google 给我的所有结果，90% 的答案说 put '%''%' ，另外 10% 的答案说更复杂。

由于某种原因，所有这些都不适合我。

我怎么记得在MySQL（phpmyadmin）中有这个内置的搜索功能，所以我尝试它只是为了看看MySQL如何处理带有撇号的搜索，结果发现MySQL只是用反斜杠转义撇号LIKE '%\' %'
那么为什么我在每个用户的查询中将撇号替换为 \' 呢？

这就是我的想法：

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();

这解决了我的问题。
如果仍然存在安全问题，请纠正我。

First of all my Search query 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.

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.

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.

回复收藏 0 原文

挽容 2024-11-24 16:33:14

括号用于标识符，因此您的代码将在 Header 表中查找字段 %'%。您想使用字符串代替。要将撇号放入字符串文字中，可以使用双撇号。

SELECT *
FROM Header WHERE userID LIKE '%''%'

Brackets are used around identifiers, so your code will look for the field %'% in the Header table. You want to use a string insteaed. To put an apostrophe in a string literal you use double apostrophes.

SELECT *
FROM Header WHERE userID LIKE '%''%'

回复收藏 0 原文

孤寂小茶 2024-11-24 16:33:14

比较DB中包含撇号的名称。

String sql="select lastname  from employee where FirstName like '%"+firstName.trim().toLowerCase().replaceAll("'", "''")+"%'"

statement = conn.createStatement();
        rs=statement.executeQuery(Sql);

通过Java代码迭代结果

Compare Names containing apostrophe in DB through Java code

String sql="select lastname  from employee where FirstName like '%"+firstName.trim().toLowerCase().replaceAll("'", "''")+"%'"

statement = conn.createStatement();
        rs=statement.executeQuery(Sql);

iterate the results.

回复收藏 0 原文

~没有更多了~