将一列字符串显示为转换为多行显示?
现在有一个字符串
/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ
参赛过一条sql语句显示为
ABC
AA
AD
ABD
JI
CC
ALSKD
ALDKDJ
8行数据。请问各位有办法实现吗?谢谢了。
方法一:支持oracle9以上
SELECT rn,
'/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/' str,
SUBSTR('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/',
LAG(INSTR('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/', '/', 1, rn),
1,
0) OVER(ORDER BY rn) + 1,
INSTR('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/', '/', 1, rn) -
LAG(INSTR('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/', '/', 1, rn),
1,
0) OVER(ORDER BY rn) - 1)
FROM (SELECT LEVEL rn
FROM DUAL
CONNECT BY 1 = 1
AND LEVEL <=
LENGTH('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/') -
LENGTH(REPLACE('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/', '/')));
方法二:支持oracle9以上
select substr('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ', sp, ep - sp) str
from (select instr('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ', '/', 1, rownum) + 1 sp,
instr('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ', '/', 1, rownum + 1) ep
from dual
connect by rownum <
LENGTH('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/') -
LENGTH(REPLACE('/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ/', '/')))
where ep > 0;
方法三:支持oracle10以上
with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
select regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum<=length(regexp_replace(id,'[^/]+'))