表结构如下
SQL> desc test;
Name Type Nullable Default Comments
—- —- ——– ——- ——–
C1 CLOB Y
SQL> select count(*) from test;
COUNT(*)
———-
1
字段中内容为
SU.SYSTEM_USER_CODE||’|#|’||S.STAFF_NAME||’|#|’||SU.STATUS_CD||’|#|’||SU.CHANNEL_ID||’|#|’||SU.LAN_ID
01|#|政企|#|1000|#|13378|#|1407
02|#|政企|#|1000|#|13383|#|1407
01|#|路|#|1100|#|10093|#|1401
54|#|2354|#|1100|#|111|#|14
55|#|2355|#|1100|#|111|#|14
56|#|2356|#|1100|#|111|#|14
57|#|2357|#|1100|#|111|#|14
58|#|2358|#|1100|#|111|#|14
59|#|2359|#|1100|#|111|#|14
用正则表达式将数据先拆分为行,再将各行拆分为列。
SQL> SELECT c1,
regexp_substr(c1, ‘[^|#]+’, 1, 1) AS d1,
regexp_substr(c1, ‘[^|#]+’, 1, 2) AS d2,
regexp_substr(c1, ‘[^|#]+’, 1, 3) AS d3,
regexp_substr(c1, ‘[^|#]+’, 1, 4) AS d4,
regexp_substr(c1, ‘[^|#]+’, 1, 5) AS d5
FROM (SELECT to_char(regexp_substr(c1, ‘[^[:space:]]+’, 1, LEVEL + 1)) AS c1
FROM test CONNECT BY LEVEL <= regexp_count(c1, chr(10)));
C1 D1 D2 D3 D4 D5
—————————————- —– —– —– —– —–
01|#|政企|#|1000|#|13378|#|1407 01 政企 1000 13378 1407
02|#|政企|#|1000|#|13383|#|1407 02 政企 1000 13383 1407
01|#|路|#|1100|#|10093|#|1401 01 路 1100 10093 1401
54|#|2354|#|1100|#|111|#|14 54 2354 1100 111 14
55|#|2355|#|1100|#|111|#|14 55 2355 1100 111 14
56|#|2356|#|1100|#|111|#|14 56 2356 1100 111 14
57|#|2357|#|1100|#|111|#|14 57 2357 1100 111 14
58|#|2358|#|1100|#|111|#|14 58 2358 1100 111 14
59|#|2359|#|1100|#|111|#|14 59 2359 1100 111 14
9 rows selected
转载请注明来源网站:blog.ytso.com谢谢!
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/tech/database/4537.html