SQL:列的拆分

最新推荐文章于 2023-06-29 15:08:56 发布

anddyhua

最新推荐文章于 2023-06-29 15:08:56 发布

阅读量2.4k

点赞数

分类专栏： # Note for SQL&PLSQL 文章标签： oracle sql 列拆分

本文链接：https://blog.csdn.net/anddyhua/article/details/40007015

版权

Note for SQL&PLSQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

表里面一个列的记录用逗号分隔，拆分成多条

比方 1 A,B,C,D

拆分后成

1 A

2 B

3 C

4 D

SQL>
SQL>     WITH t AS
  2      (SELECT '2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe,afe]' col
  3        FROM   dual
  4        UNION ALL
  5        SELECT '2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe]'
  6        FROM   dual
  7        UNION ALL
  8        SELECT '2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsafe,adfe,feaf,aefae,dfe,sfe,afc]'
  9        FROM   dual)
 10      SELECT col,
 11             regexp_substr(col,
 12                           '[^][,]+',
 13                           1,
 14                           LEVEL + 1) AS ctx
 15      FROM   t
 16      CONNECT BY PRIOR dbms_random.value IS NOT NULL
 17                 AND LEVEL <= regexp_count(col,
 18                                           ',')
 19                 AND PRIOR col = col;
 
COL                                                                              CTX
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa afe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928493,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa test
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 23
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 3543
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4543.k
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa 4353
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sdfkj
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sadfsi
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa esf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dsfa
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa ewfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadfi
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fadf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa fsafe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa adfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa feaf
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa aefae
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa dfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa sfe
2928494,[test,23,3543,4543.k,4353,sdfkj,sadfsi,esf,dsfa,ewfe,fadfi,fadf,adfe,fsa afc
 
59 rows selected