Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

案例來源: fb pl/sql 社團

案例一

table;
column
ORACLE
I want output
oracle
racle
acle
cle
le
e

用level解

****************

create table test_tt (name varchar2(20));
insert into test_tt values('oracle');
select substr(name,level,length(name)) from test_tt connect by level <=length(name);

案例二

table;
column
ORACLE
I want output
O
R
A
C
L
E

level解

***************

select substr(name,level,1) from test_tt connect by level <=length(name);

案例三

table
-------
col
----
100,200,300,400 
I want to output below??????????? 
output
col
-------
100
200
300
400

用substr+instr+level解

************************************

with t as ( select '100,200,300,400' col from dual)
SELECT SUBSTR(','||col||',', INSTR(','||col||',',',', 1, level)+1,
INSTR(','||col||',',',',1,level+1)-INSTR(','||col||',',',',1,level)-1) col
FROM t connect by level<=4;

*加入前後兩個','方便解題

*level控制幾個','

* 邏輯: 取值 , 第幾個逗號到底幾個逗號    

*