程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> oracle 字符串轉成行

oracle 字符串轉成行

編輯:關於Oracle數據庫
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表示串裡面有多少字符。
當是字符串是表中的字段時,取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, '','', 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;

注:如果是上面代碼是遠程的代碼,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= '||v_c||') C,不要用dual表,可以改用all_objects或user_objects, 要不然能查詢,但是把查詢出來的SQL插入到某個表時,只能插入一行

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved