SELECT REGEXP_SUBSTR('1,2,3,4,5,6,7,8,9', '[^,]+', 1, ROWNUM) FROM DUAL CONNECT BY ROWNUM <= LENGTH('1,2,3,4,5,6,7,8,9') - LENGTH(REPLACE('1,2,3,4,5,6,7,8,9', ',', '')) + 1
分析讲解：
oracle中REGEXP_SUBSTR方法的使用
注：本内容来源于《oracle中REGEXP_SUBSTR方法的使用》
有一张tt_ticket表，用来存机票信息。里边有一个字段叫schedule，表示的是行程，存储格式为：北京/虹桥
由于公司位于上海。而上海眼下有两个机场：浦东和虹桥。所以对于出发地或到达地为上海的机票来说，行程中会存虹桥或浦东，当然。有时候可能也会直接存上海(可能性非常小。但不代表没有)。这样，行程对于出发地为上海的行程来说。可能有下面几种可能：
a.虹桥/北京
b.浦东/北京
c.上海/北京
如今要实现搜索出发城市为上海时，把这三条信息所有拉出来。
首先，创建一张城市地区映射表tt_ticket_city_mapper,包括字段city和mapper_city,存储下面数据：
city mapper_city 虹桥 上海 浦东 上海 上海 上海 下面先把sql贴出来，是mybatis中的部分代码：
select * from tt_ticket where 1=1 <if test="departureCity !='' and departureCity !=null"> <![CDATA[ and (nvl( (select m.mapper_city from tt_ticket_city_mapper m where m.city = trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,1) from dual))) , trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,1) from dual)) ) = nvl( (select m.mapper_city from tt_ticket_city_mapper m where m.city = trim(#{departureCity}) ), #{departureCity} ) ) ]]> </if> <if test="arriveCity !='' and arriveCity !=null"> <![CDATA[ and(nvl( (select m.mapper_city from tt_ticket_city_mapper m where m.city = trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,2) from dual)) ) , trim((select REGEXP_SUBSTR(t.schedule,'[^/]+',1,2) from dual)) ) = nvl( (select m.mapper_city from tt_ticket_city_mapper m where m.city = trim(#{arriveCity}) ), #{arriveCity} ) ]]> </if>先解释一下：select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual
比方：
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,1) from dual
返回的是虹桥
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,2) from dual
返回的是北京
第一个參数是要解析的字符串，第二个是正在表达式，第三个表示字符串解析时的起始位置。比方：
select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,2,1) from dual
返回的是桥
第四个參数表示取拆分后数组里的第几个值。
这种话
nvl(
(select m.mapper_city
from tt_ticket_city_mapper m
where m.city = trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual))
) ,
trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual))
)
就能够取出/前面的字符串，比方浦东或虹桥，然后在 tt_ticket_city_mapper中查询得到器映射的城市上海。假设没有的话就是它自己
等于号后边相同是做一下转换。
当然，看到这，肯定会有人说为什么不直接存出发城市和到达城市两个字段？那是由于行程可能是 北京/香港/迈阿密/香港/北京 这种数据。
Oracle中REGEXP_SUBSTR及其它支持正则表达式的内置函数小结
注：本内容来源于《Oracle中REGEXP_SUBSTR及其它支持正则表达式的内置函数小结》
Oracle中REGEXP_SUBSTR函数的使用说明：
题目如下:
在oracle中，使用一条语句实现将'17,20,23'拆分成'17'，'20'，'23'的集合。
REGEXP_SUBSTR函数格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式，匹配的值将返回，返回策略由__occurrence决定
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier ：模式（'i'不区分大小写进行检索，'c'区分大小写进行检索。默认为'c'。）
1、查询使用正则分割后的第一个值，也就是17
[sql]
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,1,'i') AS STR FROM DUAL;
结果:
STR
-----
17
2、查询使用正则分割后的最后一个值，也就是23
[sql]
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL;
结果:
STR
----
23
3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来
[sql]
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;
结果:
LEVEL
----
1
2
3
4
5
6
7
4、将上面REGEXP_SUBSTR的occurrence关联
[sql]
SELECT NVL(REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i'), 'NULL') AS STR
FROM DUAL
CONNECT BY LEVEL <= 7;
结果:
STR
----
17
20
23
NULL
NULL
NULL
NULL
5、优化上面的SQL语句，让生成的行的数量符合实际情况
[sql]
SELECT REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <=
LENGTH('17,20,23') - LENGTH(REGEXP_REPLACE('17,20,23', ',', ''))+1;
结果:
STR
----
17
20
23
Oracle正则表达式(regularexpression)
元字符
字符含义
举例说明
^
匹配字符串的开始位置(在[]中使用，此时它表示不接受该字符集合。
^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:]]
[[:digit:]] 任何数字。
[[:alnum:]]
任何字母和数
[[:space:]]
任何白字符。
[[:upper:]]
任何大写字母。
[[:lower:]]
任何小写字母。
[[:punct:]]
任何标点符号。
[[:xdigit:]]
任何16进制的数字，相当于[0-9a-fA-F]。
Oracle支持正则表达式的内置函数
名称
语法
备注
REGEXP_LIKE
REGEXP_LIKE
(source_string,
pattern
[, match_parameter]
)
source_string：源字符串
Pattern：正则表达式
match_parameter：匹配模式(i:不区分大小写；c:区分大小写；n:允许使用可以匹配任意字符串的操作符；m:将x作为一个包含多行的字符串。
REGEXP_REPLACE
REGEXP_REPLACE
(source_string,
pattern
[,replace_string]
[,position]
[,occurtence]
[,match_parameter]
)
replace_string：用于替换的字符串
Position：开始搜索的起始位置
occurtence指定替换第n次出现字符串
其他同上。
REGEXP_SUBSTR
REGEXP_SUBSTR
(source_string, pattern
[,position
[,occurrence
[,match_parameter]]]
)
Position：指定在字符串中准确位置,即开始匹配的字符的位置，默认为1
Occurrence：指定在源字符串匹配过程中相对其他字符串，哪个字符串应该匹配。例如
select regexp_substr('The zip code80831 is for falcon, co',
'[[:alpha:]]{3,}',
1,
3)
from dual;
结果选择的是code而非The或zip。
select regexp_substr('The zip code80831 is for falcon, co',
'[[:alpha:]]{3,}',
11,//表示从第11个字符（code的d字符）开始
1)//查找到第一个匹配的就是for了，第二个匹配的是falconfrom dual;
结果是for
REGEXP_INSTR
REGEXP_INSTR
(source_string,
pattern
[,start_position
[,occurrence
[,return_option
[,match_parameter]]]]
)
start_position：
开始搜索位置
Occurrence：
第n次出现pattern，默认为1
return_option：
0：pattern的起始位置
1：pattern下一个字符起始位置
默认为0
REGEXP_COUNT
REGEXP_COUNT
(source_string,
pattern
[[,start_position]
[,match_parameter]])
11g新增的函数，表示pattern在原字符串中出现的次数
start_position：
开始搜索的位置
Oracle支持正则表达式函数举例。
Regexp_substr：用来提取一个字符串的一部分。
Regexp_substr('first filed, second filed, thirdfiled', [^,]*,')
Pattern为', [^,]*,'表示以，开始逗号，中间空一个格，连续0或多个非逗号字符，最后还是逗号，这样，返回则为', second filed,'。
-------------------------------------------------------------------------------
Regexp_instr:用来pattern的起始位置。
如有一组地址，返回邮编的位置
regexp_instr('Jone Smith,10045 Berry Lane,SanJoseph,CA
91234-1234','[[:digit:]]{5}([-[[:digit:]]{4}])?$')
显然结果位置是91234-1234对应的起始位置。
-------------------------------------------------------------------------------
Regexp_replace：用来将pattern替换成需要替换的字符串，相比传统的replace函数，该函数相比较灵活。例如：
'Jone smith'中有三个空格，而'Jone smith'有2个空格，要将中间的空格改为一个空格，使用replace就要使用2次，而是用正则表达式，我们就就可以将pattern设计为
'( ){2,}'这样
regexp_replace(mc,'( ){2,}',' ')
-------------------------------------------------------------------------------
regexp_like是增强版的like，可以包含_和%通配符，使用在where条件中。
正则表达式特性---后向引用
通过将自表达式匹配的内容缓存到缓冲区中，从左相右进行编号，以/digit（digit为1-9）。而子表达式用圆括号表示。
1.在regexp_replace中应用：
将字符串'aa bb cc'反写成'cc bb aa'则可以设计如下：
regexp_replace('aa bbcc','(.*) (.*) (.*)','3 2 1')
(.*)则表示任何一个字符串组合，三个这样的组合用空格隔开，与源字符串吻合，1,2,3则分别存储了三个字表达式的值，这样我们就可以使用这个达到目的。
2.在regexp_substr中应用：
查找被空格隔开的重复字母数字值
regexp_substr('the finaltest is is the implention',([[:alnum:]]+)([[:space:]]+)1')
返回结果为is is 。([[:alnum:]]+)([[:space:]]+)返回很多字符串，但是增加一个1就表示连续返回两个同样字符串，这样就把重复的串给找出来。
oracle进阶之connect by笔记
注：本文来源于《oracle进阶之connect by笔记》
oracle进阶之connect by笔记
connect by 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。
语法格式:
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}特殊词讲解：
start with: 指定起始节点的条件
connect by: 指定父子行的条件关系
prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ，
nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条
循环行: 该行只有一个子行，而且子行又是该行的祖先行
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是
level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点
自定义数据:
-- 创建表 create table employee( emp_id number(18), lead_id number(18), emp_name varchar2(200), salary number(10,2), dept_no varchar2(8) ); -- 添加数据 insert into employee values('1',0,'king','1000000.00','001'); insert into employee values('2',1,'jack','50500.00','002'); insert into employee values('3',1,'arise','60000.00','003'); insert into employee values('4',2,'scott','30000.00','002'); insert into employee values('5',2,'tiger','25000.00','002'); insert into employee values('6',3,'wudde','23000.00','003'); insert into employee values('7',3,'joker','21000.00','003');commit;
数据列表展示:
数据树形展示:
(1) 查询以emp_id为0开始的节点的所有直属节点
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary from employee start with lead_id=0 connect by prior emp_id = lead_id
-- 等同于
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with emp_id=1
connect by prior emp_id = lead_id
(2) 以emp_id为6的所有祖先节点
select emp_id,lead_id,emp_name,salary from employee start with emp_id=6 connect by prior lead_id=emp_id;
(3) 查询一个节点的叔叔伯父节点
with t as ( select employee.*,prior emp_name,level le from employee start with lead_id = 0 connect by lead_id=prior emp_id ) select * from t left join t tt on tt.emp_id=6 where t.le = (tt.le-1) and t.emp_id not in (tt.lead_id)
(4) 查询族兄
with t as ( select employee.*,prior emp_name,level le from employee start with lead_id=0 connect by lead_id= prior emp_id ) select t.* from t t left join t tt on tt.emp_id=6 where t.le=tt.le and t.emp_id<>6;
(5) level伪列的使用，格式化层级
select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level from employee start with lead_id=0 connect by prior emp_id=lead_id
level数值越低级别越高
(6) connect_by_root 查找根节点
select connect_by_root emp_name,emp_name,lead_id,salary from employee where dept_no='002' start with lead_id=1 connect by prior emp_id = lead_id;
(7) 标注循环行
-- 插入一条数据，与另一条emp_id=7的数据组成循环行 insert into employee values('3',7,'joker_cycle','21000.00','003'); commit; -- connect_by_iscycle("CYCLE"), connect by nocycle select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle from employee start with lead_id=0 connect by nocycle prior emp_id = lead_id;
(8) connect_by_isleaf 是否是叶子节点
select emp_id,emp_name,lead_id,salary,connect_by_isleaf from employee start with lead_id=0 connect by nocycle prior emp_id=lead_id;
