Oracle splits comma-separated strings to implement split

Recently, some people often ask the same sql implementation method, send it on itpub to take care of Baidu users.

If a string is separated by symbols like commas or other symbols, you want to break it into columns, such as' first field, second field, third field'.

Disassemble into

First field

Second field

Third field

The first regular expression supported with 10G

Anbob@ANBOB > SELECT REGEXP_SUBSTR ('first field, second field, third field',' [^,] +', 1 FROM DUAL 3 CONNECT BY ROWNUM select * from test11) ID NAME--1 a recollection brecrium c 2 e recorder dnbobcentury ANBOB > with temp0 as (select LEVEL lv from dual CONNECT BY LEVEL insert into testreg values ('911 meme 000 min12 meme 31')

1 row created.

Anbob@NCME > insert into testreg values ('11911 dint 554000312931')

1 row created.

Anbob@NCME > commit

Commit complete.

Anbob@NCME > select ltrim (regexp_replace (','| | vJournal'([,])','\ 100'),',') newv,v from testreg

NEWV V

--

00911,00000,0012,0031 911,000,12,31

0011911,00554000,00312,00931 11911,554000312931

My original link is on http://www.anbob.com/archives/221.html.