Query all values in a comma-separated string in oracle

Query all values in a comma-separated string in oracle. If a string is separated by a comma or another symbol, you want to split it into columns, such as 'first field, second field, third field ', www.2cto.com is split into first field second field third field the first Regular Expression www.2cto.com SELECT REGEXP_SUBSTR ('first field, second field, third field ',' [^,] + ', 1, rownum) from dual connect by rownum <= LENGTH ('first field, second field, third field ')-LENGTH (REPLACE ('first field, second field, third field ', ',', ") + 1 ---- first field second field third field REGEXP_SUBSTR function is to intercept the string starting with a regular character, not a comma, the second parameter is the number of groups, the rownum pseudo column number, the connect loop, the number of cycles is the total length of the string-after removing the separator = several separators + 1 www.2cto.com second type, function 1: CREATE a Type create or replace type type_split is table of VARCHAR2 (4000). 2. CREATE the create or replace function split (p_list varchar2, p_sep varchar2: = ',') return type_split pipelined IS l_idx pls_integer; v_list varchar2 (50): = p_list; begin loop l_idx: = instr (v_list, p_sep); if l_idx> 0 then pipe row (substr (v_list, 1, l_idx-1); v_list: = substr (v_list, l_idx + length (p_sep); else pipe row (v_list); exit; end if; end loop; end split; third, debug www.2cto.com select * from table (split ('aaa, bbb, ccc ',', ') Extension regexp_replace www.2cto.com, if it does not start with 9, add '00' anbob @ NCME> create table testreg (v varchar2 (80); Table created to the group string. anbob @ NCME> insert into testreg values ('1970, 12, 31'); 1 row created. anbob @ NCME> insert into testreg values ('1970, 000,312,931'); 1 row created. anbob @ NCME> commit; Commit complete. anbob @ NCME> select ltrim (regexp_replace (',' | v, '([,])', '\ 100'), ',') newv, v from testreg; newv v ---------- 911,000, 312,931