将表中一个字段拆分成多列 oracle db2字段拆分

最新推荐文章于 2023-11-12 22:33:01 发布

置顶 he251377753

最新推荐文章于 2023-11-12 22:33:01 发布

阅读量4.5k

点赞数

本文链接：https://blog.csdn.net/he251377753/article/details/39340393

版权

--oracle 数据库拆分办法

WITH TT(bbbh,
USER_,
sjly) AS
(SELECT 00 bbbh, '' USER_, 'a^b^c' || '^' sjly
    from dual
UNION ALL
SELECT bbbh,
         SUBSTR(TT.sjly, 1, Instr(TT.sjly, '^') - 1),
         SUBSTR(TT.sjly, Instr(TT.sjly, '^') + 1)
    from TT
   WHERE LENGTH(TT.sjly) > 1)

SELECT bbbh,
substr(TRIM(USER_), Instr(TRIM(USER_), ',') + 1, length(USER_)) fy
FROM TT
WHERE USER_ is not null
ORDER BY USER_, bbbh

--db2 数据库拆分办法

WITH N(STRING,ORI,POS) AS (
VALUES('AAA,BBB,CCC,DDD,EEE,',1,POSSTR('AAA,BBB,CCC,DDD,EEE,',','))
UNION ALL
SELECT STRING,POS+1,LOCATE(',',STRING,POS+1) FROM N
WHERE LOCATE(',',STRING,POS+1)>0
)
SELECT STRING,ORI,POS,SUBSTR(STRING,ORI,POS-ORI) FROM N