SELECT SUBSTR (T.RPT_ID, INSTR (T.RPT_ID,',1,C.LV)+ 1,C.LV + 1)- (INSTR (T.RPT_ID,C.LV)+ 1)) AS RPT_ID FROM (SELECT ',' || '85,86,87' || ',' RPT_ID, LENGTH ('85,') - NVL (LENGTH (REPLACE ('85,86',')),0) CNT FROM DUAL) t, (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= LENGTH('85,') - NVL(LENGTH(REPLACE('85,87',0)) c WHERE T.cnt >= c.lv 说明:CNT表示串里面有多少字符。 当是字符串是表中的字段时,取level<=最大个数 如: 1).取最大个数 SELECT MAX(LENGTH(RPT_ID || ',') - NVL(LENGTH(REPLACE(RPT_ID,0)) INTO v_c FROM DIM_AUDIT_TABLE@sjmh_inter; 2).l转成行 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,'',C.LV) + 1,C.LV + 1) - (INSTR(T.RPT_ID,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;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|