SELECT SUBSTR (T.RPT_ID,
INSTR (T.RPT_ID,',',1,C.LV)+ 1,
INSTR (T.RPT_ID,',',1,C.LV + 1)- (INSTR (T.RPT_ID,',',1,C.LV)+ 1))
AS RPT_ID
FROM (SELECT ',' || '85,86,87' || ',' RPT_ID,
LENGTH ('85,86,87' || ',') - NVL (LENGTH (REPLACE ('85,86', ',')), 0) CNT
FROM DUAL) t,
(SELECT LEVEL lv
FROM DUAL
CONNECT BY LEVEL = LENGTH('85,86,87' || ',') - NVL(LENGTH(REPLACE('85,86,87', ',')), 0)) c
WHERE T.cnt >= c.lv
說明：CNT表示串里面有多少字符。
當(dāng)是字符串是表中的字段時(shí)，取level=最大個(gè)數(shù)
如：
１）.取最大個(gè)數(shù)
SELECT MAX(LENGTH(RPT_ID || ',') -
NVL(LENGTH(REPLACE(RPT_ID, ',')), 0)) INTO v_c
FROM DIM_AUDIT_TABLE@sjmh_inter;
２）.l轉(zhuǎn)成行
SELECT T.T_NAME,
T.T_NAME_COMM,
T.T_COLUMN,
T.T_COLUMN_COMM,
t.COMMENT_NL,
t.COMMENT_NL_TIME,
t.SEQ_USER_ID,
SUBSTR(T.RPT_ID,
INSTR(T.RPT_ID, '','', 1, C.LV) + 1,
INSTR(T.RPT_ID, '','', 1, C.LV + 1) -
(INSTR(T.RPT_ID, '','', 1, C.LV) + 1)) AS RPT_ID
FROM (SELECT A.T_NAME,
A.T_NAME_COMM,
A.T_COLUMN,
A.T_COLUMN_COMM,
a.COMMENT_NL,
a.COMMENT_NL_TIME,
a.SEQ_USER_ID,
'','' || A.RPT_ID || '','' RPT_ID,
LENGTH(A.RPT_ID || '','') -
NVL(LENGTH(REPLACE(A.RPT_ID, '','')), 0) CNT
FROM DIM_AUDIT_TABLE@sjmh_inter A
WHERE a.COMMENT_NL is not null) T,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL = '||v_c||') C
WHERE C.LV = T.CNT;
注:如果是上面代碼是遠(yuǎn)程的代碼,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL = '||v_c||') C,不要用dual表,可以改用all_objects或user_objects, 要不然能查詢,但是把查詢出來的SQL插入到某個(gè)表時(shí),只能插入一行
您可能感興趣的文章:- Oracle 函數(shù)大全[字符串函數(shù)，數(shù)學(xué)函數(shù)，日期函數(shù)]
- Oracle 存儲(chǔ)過程總結(jié) 二、字符串處理相關(guān)函數(shù)
- Oracle 多行記錄合并/連接/聚合字符串的幾種方法
- ORACLE常用數(shù)值函數(shù)、轉(zhuǎn)換函數(shù)、字符串函數(shù)
- ADO.NET 連接數(shù)據(jù)庫字符串小結(jié)(Oracle、SqlServer、Access、ODBC)
- Oracle中SQL語句連接字符串的符號使用介紹
- Oracle中字符串連接的實(shí)現(xiàn)方法
- 深入分析C#連接Oracle數(shù)據(jù)庫的連接字符串詳解
- ORACLE 超長字符串問題的解決辦法
