在LIKE中转义通配符 [英] Escaping wildcards in LIKE
问题描述
在Oracle中使用SQL LIKE
运算符时如何转义通配符(_
和%
)?
How do I escape wildcards (_
and %
) when using a SQL LIKE
operator in Oracle?
我今天遇到一个愚蠢的问题.我需要使用LIKE
在varchar列上搜索下划线_
的存在.正如预期的那样,它不起作用-因为根据SQL,下划线是通配符.这是我的(简化)代码:
I came to a silly issue today. I need to search for the presence of an underscore _
on a varchar column using LIKE
. It doesn't work -- as expected -- since underscores are wildcards according to SQL. Here's my (simpified) code:
create table property (
name varchar(20),
value varchar(50)
);
insert into property (name, value) values ('port', '8120');
insert into property (name, value) values ('max_width', '90');
insert into property (name, value) values ('taxrate%', '5.20');
我在PostgreSQL中尝试了以下查询,它们返回了我想要的行:
I tried the following queries in PostgreSQL and they return the rows I want:
select * from property where name like '%\_%'; -- should return: max_width
select * from property where name like '%\%%'; -- should return: taxrate%
不幸的是,它在Oracle 12c中不起作用.有没有通配符转义的标准"方法?还是至少在Oracle中有效?
Unfortunately it doesn't work in Oracle 12c. Is there a "standard" way of escaping wildcards? Or at least something that works in Oracle?
推荐答案
You can use the escape
syntax
您可以使用
ESCAPE
子句在模式中包含实际字符%
或_
,该子句标识转义字符.如果转义字符在模式中的字符%
或_
之前,则Oracle会在模式中按字面意义解释该字符,而不是将其解释为特殊的模式匹配字符.
You can include the actual characters
%
or_
in the pattern by using theESCAPE
clause, which identifies the escape character. If the escape character precedes the character%
or_
in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character.
因此您可以这样做:
select * from property where name like '%\_%' escape '\';
NAME VALUE
-------------------- --------------------------------------------------
max_width 90
select * from property where name like '%\%%' escape '\';
NAME VALUE
-------------------- --------------------------------------------------
taxrate% 5.20
这篇关于在LIKE中转义通配符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!