use regular expression in the oracle sqlplus

来源：互联网发布：linux ssh端口22修改编辑：程序博客网时间：2024/06/04 19:10

SQL> select Customer_id from CUSTOMER_PUBLIC_INFO where regexp_like(customer_id
,'BJBJ[^123456789]*1$');

CUSTOMER_ID
----------------------------------------
BJBJ00000001

oracle 数据库目前内建了符合posix 标准的正则表达式。

四个新的函数分别是：regexp_like、regexp_instr、regexp_substr、和regexp_replace。它们在用法上与oracle sql 函数like、instr、substr 和replace用法，但是它们使用posix 正则表达式代替了老的百分号（%）和通配符（_）字符。

posix 正则表达式由标准的元字符（metacharacters）所构成：

'^' 表示字符串的开始
'$' 表示字符串的结束
'.' 表示任何字符
字符的范围，比如说'[a-z]'，表示任何ascii 小写字母，与字符类"[[:lower:]]"" 等价
'?' 允许一个后继字符匹配零次或一次
'+' 允许一个后继字符匹配一次或多次
'*' 表示零次或多次

可以使用"{m,n}" 指定一个精确地出现范围，其意思是“出现从m 次到n 次”；"{m}" 表示“正好m次”；而"{m,}"表示“至少m次”。还可以使用圆括号组合字符的集合，使用"|"（竖线）表示可替换。例如，字符串'^([a-z]+|[0-9]+)$'将匹配所有由小写字母或数字组合成的字符串。

regexp_like 与like 操作符相似。如果第一个参数匹配正则表达式它就解析为true。例如whereregexp_like(ename,'^j[ao]','i') 将在ename 以ja 或jo 开始的情况下返回一行数据。'i'参数指定正则表达式是大小写敏感的。另外还可以在check 约束和函数索引中指定regexp_like。例如：

alter table emp add constraint regex01

check (regexp_like(ename,'^[[:alpha:]]+$'));

这条语句使得ename 字段只能包含字母和数字字符（也就是说没有空格或者标点符号）。试图插入或者更新这些数据将导致一个ora-2290 异常，或者检查约束的有效性。

regexp_instr 与instr 函数类似。它返回一个字符串中匹配一个正则表达式的第一个子串的开始位置。例如：

select regexp_instr('the total is $400 for your purchase.','$[[:digit:]]+')

from dual;

这个查询返回14，即$400在字符串的开始位置。另外还可以指定子串出现的次数；开始搜索的位置；是返回匹配的位置还是返回匹配之后字符的位置。

regexp_substr 返回匹配一个正则表达式的子串。虽然结合使用substr 和regexp_instr 及length 也可以实现这一功能，但是使用这个函数却更为简单。

select regexp_instr('one,two,three','[^,]*') from dual;

这个查询返回'one'，将第一个参数看成一个逗号分隔的列表并返回第一个逗号之前的所有字符。

regexp_replace 返回初始参数被匹配子串替换之后的结果。例如：

select regexp_replace('the temperature is 23°f',

'([[:digit:]])+°f',

('/1'-32)*5/9||'°c')

from dual;

这个查询将查找一个华氏温度并将其转换为摄氏度。它将返回：'the temperature is -5°c'。