Oracle中REGEXP_LIKE与LIKE的区别
2021-10-13 14:48:03
LIKE 是 标准的 SQL 处理。 SQL Server, DB2 , MySQL 等大部分数据库， 都支持的写法。
REGEXP_LIKE 是 Oracle 特有的，
下面是一些 REGEXP_LIKE 使用的例子
测试表
CREATE TABLE test_reg_like ( a varchar(20) );
INSERT INTO test_reg_like VALUES('ABC');
INSERT INTO test_reg_like VALUES('A12');
INSERT INTO test_reg_like VALUES('12a12');
3个参数
第一个是输入的字符串
第二个是正则表达式
第三个是取值范围：
i：大小写不敏感；
c：大小写敏感；
n：点号 . 不匹配换行符号；
m：多行模式；
x：扩展模式，忽略正则表达式中的空白字符。
全部测试数据
SQL> SELECT * FROM test_reg_like;
A
----------------------------------------
ABC
A12
12a12
匹配字母A的
SQL> SELECT
2 *
3 FROM
4 test_reg_like
5 WHERE
6 REGEXP_LIKE(a, 'A');
A
----------------------------------------
ABC
A12
匹配字母A的 （大小写不敏感）
SQL> SELECT
2 *
3 FROM
4 test_reg_like
5 WHERE
6 REGEXP_LIKE(a, 'A', 'i');
A
----------------------------------------
ABC
A12
12a12
匹配字母A 后面跟1个或多个数字的 （大小写不敏感）
SQL> SELECT
2 *
3 FROM
4 test_reg_like
5 WHERE
6 REGEXP_LIKE(a, 'A\d+', 'i');
A
----------------------------------------
A12
12a12
匹配字母A开头，数字结尾的
SQL> SELECT
2 *
3 FROM
4 test_reg_like
5 WHERE
6 REGEXP_LIKE(a, '^A.+\d$');
A
----------------------------------------
A12
CREATE TABLE test_phone (
phone_number varchar(20)
);
INSERT INTO test_phone
SELECT '13812345678' FROM dual UNION ALL
SELECT '13812345566' FROM dual UNION ALL
SELECT '13812345656' FROM dual UNION ALL
SELECT '13812345556' FROM dual UNION ALL
SELECT '13812346666' FROM dual UNION ALL
SELECT '13855661234' FROM dual UNION ALL
SELECT '13856561234' FROM dual UNION ALL
SELECT '13855561234' FROM dual UNION ALL
SELECT '13866661234' FROM dual;
-- AAAA 结尾的
SELECT
*
FROM
test_phone
WHERE
REGEXP_LIKE(REVERSE(phone_number), '^(\d)\1\1\1');
PHONE_NUMBER
----------------------------------------
13812346666
-- AABB 结尾的
SELECT
*
FROM
test_phone
WHERE
REGEXP_LIKE(REVERSE(phone_number), '^(\d)\1([^\1])\2')
AND NOT REGEXP_LIKE(REVERSE(phone_number), '^(\d)\1\1\1');
PHONE_NUMBER
----------------------------------------
13812345656
-- ABAB 结尾的
SELECT
*
FROM
test_phone
WHERE
REGEXP_LIKE(REVERSE(phone_number), '^(\d)(\d)\1\2')
AND NOT REGEXP_LIKE(REVERSE(phone_number), '^(\d)\1\1\1');
PHONE_NUMBER
----------------------------------------
13812345656
2023-05-17 04:33:51
Oracle中的Like操作符使用'_'和'%'作为
通配符，使用就像这样：SELECT name FROM test_like WHERE name like '_a%';
即匹配test_like表name列中第2个字母是a的所有行。但是注意，Oracle匹配时区分大小写的。也就是说上面的查询时无法查询到name='SAas'这行的。
Oracle10g中提供的正则表达式功能可以很好的解决这个问题，当然这不是
使用正则表达式函数的唯一优点，实际上它比Like操作符强大的多。正则表达式的语法就不用多说了，现在大多数语言都支持正则表达式了。
下面主要介绍下Oracle中正表达式函数REGEXP_LIKE的使用：
REGEXP_LIKE(x, pattern [, match_option])
当源
字符串x匹配正则表达式pattern时，返回true。可以使用match_option修改默认匹配选项，该参数可以被设置为：- 'c'， 说明在进行匹配时区分大小写（默认选项）
- 'i'， 说明在进行匹配时不区分大小写
- 'n' 允许使用可以匹配任意字符的操作符（通常是'.'）
- 'm'， 将x作为一个包含多行的字符串
举个例子：
SELECT * FROM test_reg WHERE REGEXP_LIKE(name, '(a)\1', 'i');
上面的SQL语句匹配test_reg表中name列含有两个连续字符'a'（不区分大小写）的行，如name='SaAs'。此外，这里我们还使用了正则表达式中的后引用语法——\n表示重复n次上次匹配的内容，此处(a)\1表示匹配两个连续的字符'a'。
需要注意的是，后引用必须使用双括号，否则会出现如下结果：
SELECT * FROM test_reg WHERE REGEXP_LIKE(name, 'a\1', 'i')
ORA-12727: 正则表达式中的后向引用无效
SQL> SELECT * FROM test_reg WHERE REGEXP_LIKE(name, '^_(a)\1', 'i');
NAME
---------- 实际应该使用：
SQL> SELECT * FROM test_reg WHERE REGEXP_LIKE(name, '^.(a)\1', 'i');
NAME
---------- SaAs
最后一点，不要混淆LIKE操作符的通配符和正则表达式的语法，也就是说不要再正则表达式中使用LIKE操作符中的通配符，如果这样做会得到未知的结果，因为'_'和'%'会被正则表达式当做普通字符进行匹配。
比如下面这条SQL想要得到name='SaAs'这条记录，但实际的查询结果为空。
2021-09-15 01:13:54
SELECT * FROM staff a WHERE a.staff_code LIKE '0___80';
SELECT * FROM staff a WHERE REGEXP_LIKE(a.staff_code,'0[0-9]{3}80');
看看这个链接
2020-06-03 10:24:09
热门标签
