SQL:列の分割
表の1つの列のレコードはカンマで区切られ、複数に分割されます.
例えば1 A,B,C,D
分割後に作成
1 A
2 B
3 C
4 D
例えば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