为什么在 LIKE 过滤器中使用下划线字符会给我所有结果? [英] Why does using an Underscore character in a LIKE filter give me all the results?

问题描述

我使用 LIKE 条件编写了以下 SQL 查询:

I wrote the below SQL query with a LIKE condition:

SELECT * FROM Manager
WHERE managerid LIKE '_%'
AND managername LIKE '%_%'

在 LIKE 我想搜索任何下划线 %_%，但我知道我的列数据没有下划线字符.

In the LIKE I want to search for any underscores %_%, but I know that my columns' data has no underscore characters.

• 为什么查询会给出表中的所有记录?

示例数据:

create table Manager(
    id int
    ,managerid varchar(3)
    ,managername varchar(50)
    );

insert into Manager(id,managerid,managername)values(1,'A1','Mangesh');
insert into Manager(id,managerid,managername)values(2,'A2','Sagar');
insert into Manager(id,managerid,managername)values(3,'C3','Ahmad');
insert into Manager(id,managerid,managername)values(4,'A4','Mango');
insert into Manager(id,managerid,managername)values(5,'B5','Sandesh');

Sql-Fiddle

推荐答案

像这样修改你的 WHERE 条件:

Modify your WHERE condition like this:

WHERE mycolumn LIKE '%\_%' ESCAPE '\'

这是 Oracle 支持转义字符的方式之一.在这里，您使用 escape 关键字定义转义字符.有关详细信息，请参阅Oracle 文档上的此链接.

This is one of the ways in which Oracle supports escape characters. Here you define the escape character with the escape keyword. For details see this link on Oracle Docs.

'_' 和 '%' 是 SQL 中 LIKE 操作语句中的通配符.

The '_' and '%' are wildcards in a LIKE operated statement in SQL.

_ 字符查找(任何)单个字符的存在.如果您按 columnName LIKE '_abc' 搜索，它会为您提供具有 'aabc'、'xabc'、 的行的结果'1abc', '#abc' 但不是 'abc', 'abcc', 'xabcd' 等等.

The _ character looks for a presence of (any) one single character. If you search by columnName LIKE '_abc', it will give you result with rows having 'aabc', 'xabc', '1abc', '#abc' but NOT 'abc', 'abcc', 'xabcd' and so on.

'%' 字符用于匹配 0 个或多个字符.这意味着，如果您按 columnName LIKE '%abc' 进行搜索，它将为您提供具有 'abc', 'aabc' 的结果，'xyzabc' 等，但没有 'xyzabcd'、'xabcdd' 和任何其他不以 ' 结尾的字符串abc'.

The '%' character is used for matching 0 or more number of characters. That means, if you search by columnName LIKE '%abc', it will give you result with having 'abc', 'aabc', 'xyzabc' and so on, but no 'xyzabcd', 'xabcdd' and any other string that does not end with 'abc'.

就您而言，您已通过 '%_%' 进行搜索.这将使该列的所有行具有一个或多个字符，即任何字符，作为其值.这就是为什么即使列值中没有 _ 也会获得所有行的原因.

In your case you have searched by '%_%'. This will give all the rows with that column having one or more characters, that means any characters, as its value. This is why you are getting all the rows even though there is no _ in your column values.

这篇关于为什么在 LIKE 过滤器中使用下划线字符会给我所有结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！