将一列字符串显示为转换为多行显示代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】将一列字符串显示为转换为多行显示

作者:Dezai.CN / 发布于2011/3/29/ 1179

现在有一个字符串
/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ
参赛过一条sql语句显示为
ABC
AA
AD
ABD
JI
CC
ALSKD
ALDKDJ
8行数据

<div>
方法一：支持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,'[^/]+'))

</div>

试试其它关键字

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)