RUI个人博客 首页>>Oracle Development>>[原] Oracle regexp_like 函数详解

[原] Oracle regexp_like 函数详解

regexp_like    
REGEXP_LIKE(source_char, pattern
            [, match_param ]
           )

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression(正则表达式) matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

This condition complies(符合) with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, refer to Appendix D, "Oracle Regular Expression Support".

  • source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the data types CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

  • pattern is the regular expression. It is usually a text literal and can be of any of the data types CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the data type of pattern is different from the data type of source_char, Oracle converts pattern to the data type of source_char. For a listing of the operators you can specify in pattern, refer to Appendix D, "Oracle Regular Expression Support".

  • match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:

    • 'i' specifies case-insensitive matching.(指定不区分大小写匹配)

    • 'c' specifies case-sensitive matching. (指定区分大小写匹配)

    • 'n' allows the period (.), which is the match-any-character wildcard character, to match the newline(换行) character. If you omit this parameter, then the period does not match the newline character.

    • 'm' treats(处理) the source string as multiple lines. Oracle interprets(解释) ^ and $ as the start and end, respectively(各自的), of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then Oracle treats the source string as a single line.

    • 'x' ignores whitespace(空格) characters. By default, whitespace characters match themselves.

    If you specify multiple contradictory values, then Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.

    If you omit match_parameter, then:

    • The default case sensitivity is determined by the value of the NLS_SORT parameter.

    • A period (.) does not match the newline character.

    • The source string is treated as a single line.

      See Also:

Examples

The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph):

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

The following query returns the last name for those employees with a double vowel(双元音) in their last name (where last_name contains two adjacent occurrences of either a, e, i, o, or u, regardless of case):

SELECT last_name
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;

LAST_NAME
-------------------------
De Haan
Greenberg
Khoo
Gee
Greene
Lee
Bloom
Feeney
对应普通的  like
regexp_like(data,'[ABC]') 就相当于(like '%A%' or like '%B%' or like '%C%');
而regexp_like(data,'[0-9a-zA-Z]+') 就相当于(like '%数字%' or like '%小写字母%' or like '%大写字母%');
注: "^"不在方括号里时表示字符串开始,这里还有一个  "$",该符号在方括号外面,表示字符串的结束。
通过具体实验来学习:
create or replace view v as 
select 'A' as data from dual union all
select 'AB' from dual union all
select 'BA' from dual union all
select 'BAC' from dual;


select * from v;
DAT
---
A
AB
BA
BAC

① regexp_like(data,'A')对应普通的 like '%A%'
select * from v where regexp_like(data,'A');

DAT
---
A
AB
BA
BAC

② regexp_like(data,'^A') 对应普通的 like 'A%'
select * from v where regexp_like(data,'^A');
DAT
---
A
AB

③ regexp_like(data,'A$') 对应普通的 like '%A'
select * from v where regexp_like(data,'A$');
DAT
---
A
BA

④ regexp_like(data,'^A$') 对应普通的 like 'A'
select * from v where regexp_like(data,'^A$');
DAT
---
A
    另一个概念是 "+" 与 "*" 。'+' 表示匹配前面的子表达式一次货多次;
                                                 '*' 表示匹配前面的子表达式零次或多次;
create or replace view v as 
select '167' as str from dual union all
select '1234567' as str from dual;

select * from v;

STR
-------
167
1234567
regexp_like(str,'16+') 加号前的子表达式是 "6",至少匹配6 一次,也就相当于(like '16%' or like '166%' or ...)
                                        等价于  like '16%'
regexp_like(str,'16*') 加号前的子表达式是 "6",至少匹配6 零次,也就是相当于(like '1%' or like '16%' or ...)
                                        等价于 like '1%'
select * from v where regexp_like(str,'16+');

STR
-------
167

select * from v where regexp_like(str,'16*');

STR
-------
167
1234567
    当   "+" "*" "^$"组合之后呢?
select * 
   from (with x0 as (select level as lv from dual connect by level <=3)
         select replace(sys_connect_by_path(lv,','),',') as s
              from x0
          connect by nocycle prior lv <>lv)
   where length(s) <=2;

insert into test values(null);

select * from test;

测试下面两句是否有影响:
select * from test where regexp_like(s,'^[12]+$');

select * from test where regexp_like(s,'^[12]*$');

分析:
regexp_like(s,'^[12]+$') 对应的是
(s like '1' or s like '2' or s like '11' or s like '22' or s like '12' or s like '21')

regexp_like(s,'^[12]*$') 对应的是
(s like '1' or s like '2' or s like '11' or s like '22' or s like '12' or s like '21' or s like '')

空字符串等价于 null,而null是不能用 like 来比较的,所以这个条件不返回值


select count(*) from test where s like '';


昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码