元字
符
字符含义举例说明^
匹配字符串的开始位置(在[]中使⽤，此时它表⽰不接受该字符集合。^a ：匹配以a 开始的字符串
[^a]：匹配不含a 的字符串-当使⽤在a-m 表⽰范围；当使⽤在第⼀个字符时表⽰连字符串，如[-abc]
$
匹配字符结束位置
‘a$'：匹配以a 结束的字符串.匹配除换⾏符 n 之外的任何单字符。
匹配前⾯的⼦表达式零次或⼀次tr(y(ing)?)：可以表⽰try 或者trying *匹配前⾯的⼦表达式零次或多次 +匹配前⾯的⼦表达式⼀次或多次
()
标记⼀个⼦表达式的开始和结束位置
a(b)*可以匹配
ab,abb,abbb,youabb (c|d)匹配c 或d
[]标记⼀个中括号表达式
[cd]匹配选择c 或d 等同于
(c|d)。匹配的是单字符，[^cd]是匹配c,d 之外的
单字符。
[a-z]表⽰所有⼩写字母
{m,n}m= <；出现次数 <=n ，'{m}'表⽰出现m 次，'{m,}'表⽰⾄少出现m 次。
|表⽰或者的关系。指明两项之间的⼀个选择
字符簇字符含义[[:alpha:]]任何字母[[:digit:]]任何数字
[[:alnum:]]任何字母和数[[:space:]]任何空⽩字符[[:upper:]]任何⼤写字母[[:lower:]]任何⼩写字母[[:punct:]]任何标点符号
[[:xdigit:]]
任何16进制的数字，相当于[0-9a-fA-F]
SQL 语句——18、正则表达式函数
Oracle 正则表达式(regularexpression)
regexp_like('S','RE','O')
S 被搜索的字符串或者列表达式RE 正则内容O 参数(可选)
idle>select ename from emp where regexp_like(ename,'S');
ENAME
----------
SMITH
JONES
SCOTT
ADAMS
JAMES
idle>select ename from emp where regexp_like(ename,'^S.*T');
ENAME
----------
SMITH
SCOTT
idle>
idle> idle>select ename from emp where regexp_like(ename,'^s.*T$','i')；忽略⼤⼩写
ENAME
-
---------
SCOTT
regexp_replace(S,'RE','NEW')
idle>select ename,regexp_replace(ename,'S..T','=') aaa from emp where regexp_like (ename,'S..T');
ENAME AAA
---------- ----------
SMITH =H
SCOTT =T
复杂⽤法
SELECT REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '\3, \1 \2')
FROM dual
REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi
该SQL 语句显⽰了⽤圆括号括住的三个单独的⼦表达式。每⼀个单独的⼦表达式包含⼀个匹配元字符(.)，并紧跟着* 元字符，表⽰任何字符（除换⾏符之外）都必须匹配零次或更多次。空格将各个⼦表达式分开，空格也必须匹配。圆括号创建获取值的⼦表达式，并且可以⽤\digit 来引⽤。第⼀个⼦表达式被赋值为\1 ，第⼆个\2，以此类推。这些后向引⽤被⽤在这个函数的最后⼀个参数 (\3, \1 \2) 中，这个函数有效地返回了替换⼦字符串，并按期望的格式来排列它们（包括逗号和空格）。表 11 详细说明了该正则表达式的各个组成部分。
REGEXP_COUNT
在Oracle的11g版本中引⼊，使⽤该函数可以统计字符串出现的次数。
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
source_char：需要进⾏正则处理的字符串
pattern：进⾏匹配的正则表达式
position：起始位置，从第⼏个字符开始正则表达式匹配（默认为1）
match_param：模式（‘i’ ⽤于不区分⼤⼩写的匹配
‘c’ ⽤于区分⼤⼩写的匹配
‘n’ 允许句点(.)作为通配符去匹配换⾏符。如果省略该参数，则句点将不匹配换⾏符
‘m’ 将源串视为多⾏。即Oracle 将^和$分别看作源串中任意位置任何⾏的开始和结束，⽽不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作⼀⾏。
‘x’ 忽略空格字符。默认情况下，空格字符与⾃⾝相匹配。
如果为match_param 指定了多个相互⽭盾的值，那么Oracle 使⽤最后⼀个值。
）
REGEXP_SUBSTR
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
String：需要进⾏正则处理的字符串
pattern：进⾏匹配的正则表达式
position：起始位置，从第⼏个字符开始正则表达式匹配（默认为1）
occurrence：标识第⼏个匹配组，默认为1modifier：模式（'i'不区分⼤⼩写进⾏检索，'c'区分⼤⼩写进⾏检索，默认为'c'）
例1：查询使⽤正则分割后的第⼀个值，也就是17
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;
STR
-----
17
例2：查询使⽤正则分割后的最后⼀个值，也就是23
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL;
STR
----
23
例3：将字符串拆成⾏
select regexp_substr('17,20,23','[^,]+',1,level) AS STR
from dual
connect by level<=regexp_count('17,20,23',',')+1
STR
----
17
20
23
例4：当表（多⾏数据）作为数据源时，将表中的某字段转换为⾏，将会出现笛卡尔乘积。
SELECT lpad('~',(LEVEL-1)*5,'~')||CKJYM1 CKJYM1,LEVEL,regexp_substr(CKJYM1,'[^,]+',1,level) CKJYM
FROM (SELECT'B10,B04' CKJYM1
FROM dual
union all
SELECT'B11,B12' CKJYM1
FROM dual ) T
CONNECT BY LEVEL<= REGEXP_COUNT(CKJYM1,'B')
CKJYM1 LEVEL CKJYM
--------------------- ------------- ----------
B10,B04 1 B10
~~~~~B10,B04 2 B04
~~~~~B11,B12 2 B12
B11,B12 1 B11
sql语句替换表中内容~~~~~B10,B04 2 B04
~~~~~B11,B12 2 B12
CONNECT BY相当于是⼀个递归的⾃连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出⼝写在CONNECT BY中。
在这⾥数据并⽆⽗⼦关系，只是要让同⼀⾏数据重复出现。因此我们的连接的条件只⽤到了表的主键CKJYM1=PRIOR CKJYM1, 此外再⽤LEVEL控制层数作为递归出⼝，就可以获得想要的数据。但OR
ACLE有个检查，如果你有前后连接条件(CKJYM1=PRIOR CKJYM1)，当同⼀⾏数据再次出现，它就会报⼀个错：ORA-01436: CONNECT BY loop in user data
为了欺骗它，这⾥⽤了⼀个PRIOR DBMS_RANDOM.VALUE(相当于获取随机数), 因为DBMS_RANDOM.VALUE每次调⽤都返回不同结果，所以它认为两⾏数据不⼀样，所以不报错了。
所以sql应改为：
SELECT lpad('~',(LEVEL-1)*5,'~')||CKJYM1 CKJYM1,LEVEL,regexp_substr(CKJYM1,'[^,]+',1,level) CKJYM FROM (SELECT'B10,B04' CKJYM1
FROM dual
union all
SELECT'B11,B12' CKJYM1
FROM dual ) T
CONNECT BY LEVEL<=
REGEXP_COUNT(CKJYM1,'B')
and CKJYM1=PRIOR CKJYM1
and prior dbms_random.value is not null
CKJYM1 LEVEL CKJYM
------------ -------- ----------
B10,B04 1 B10
~~~~~B10,B04 2 B04
B11,B12 1 B11
~~~~~B11,B12 2 B12
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论