Oralce comma separated into multiple rows

Oralce comma split into multiple rows method 1 select. *, REGEXP_SUBSTR (. rolecode, '[^,] +', 1, l) AS rolecode from p_user a, (select level l from dual connect by level <= 100) bWHERE l <= LENGTH (. rolecode)-LENGTH (REPLACE (rolecode, ',') + 1 www.2cto.com use the REGEXP_SUBSTR function to split the string: the first of the five parameters is the input string, the second is the regular expression, and the third is the identification starting from the first character. (1 by default) The fourth is to identify the matching groups. (1 by default) The fifth is the value range: www.2cto.com I: Case Insensitive; c: Case Sensitive; n: point number. do not match line feed characters; m: multi-line mode; x: Extended Mode. Ignore the blank characters in the regular expression. SELECT. *, REGEXP_SUBSTR (servicereqid, '[^;] +', 1, l) AS servicereq FROM sum_portal_satisfaction a, (select level l from dual connect by level <= 100) bWHERE l <= LENGTH (servicereqid)-LENGTH (REPLACE (servicereqid, ';') + 1 order by 100; ---- select level l from dual connect by level <=; generate data rows from 1 to 100. ---- L <= LENGTH (servicereqid)-LENGTH (REPLACE (servicereqid, ';') + 1. Note that 'L' is not '1 ', the fourth parameter of the above REGEXP_SUBSTR is also the same. --- The following is a split string, and then the row is converted to create or replace view into asselect survey_type, survey_time, center_code, center_name, city_id, city_name, REGEXP_SUBSTR (servicereqid, '[^;] + ', 1, l) AS servicereqid, REGEXP_SUBSTR (servicereqname,' [^;] + ', 1, l) AS servicereqname, sum (decode (survey_value, 0, sur_times, null) giveup_times, -- Investigation value-1: unprocessed 0: the user gives up 1: Very satisfied 2. satisfied 3. not satisfied with csr 4. not satisfied with others sum (decode (survey_value, 1, sur_times, null) vsatis_times, sum (decode (survey_value, 2, sur_times, null) satis_times, sum (decode (survey_value, 3, sur_times, null) ncsr_times, sum (decode (survey_value, 4, sur_times, null) nelse_times, sum (sur_times) sur_times FROM sum_portal_satisfaction, (select level l from dual connect by level <= 100) bWHERE l <= LENGTH (servicereqid)-LENGTH (REPLACE (servicereqid, ';') + 1 group bysubslevelid, center_code, center_name, city_id, city_name, survey_type, survey_time, servicereqid, servicereqname, l Method 2: create table testTable (id nvarchar2 (200) primary key not null, content nvarchar2 (200) not null) insert into testTable values ('4', 'idx_10 idx_11 idx_12 '); select * from table (CAST (fn_split ('idx_1 $ idx_2 outside the Hall $ idx_3 $ '),' $ ') as ty_str_split )) select * from testtable B left join table (fn_split (content), '$') a on 1 = 1; -- Implement the split function create or replace type ty_str_split is table of VARCHAR2 (4000); create or replace function fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_splitIS j INT: = 0; I INT: = 1; len INT: = 0; len1 INT: = 0; str VARCHAR2 (4000); str_split ty_str_split: = ty_str_split (); BEGIN len: = LENGTH (p_str); len1: = LENGTH (p_delimiter); WHILE j <len LOOP j: = INSTR (p_str, p_delimiter, I); IF j = 0 THEN j: = len; str: = SUBSTR (p_str, I); str_split.EXTEND; str_split (str_split.COUNT): = str; IF I> = len THEN EXIT; END IF; ELSE str: = SUBSTR (p_str, I, j-I); I: = j + len1; str_split.EXTEND; str_split (str_split.COUNT): = str; END IF; END LOOP; RETURN str_split; END fn_split; declare cursor c ISselect * from table (CAST (fn_split ('idx_1 $ idx_2 $ idx_3 $ '),' $ ') as ty_str_split )); r c % ROWTYPE; begin open c; loop fetch c INTO r; exit when c % NOTFOUND; DBMS_OUTPUT.put_line (r. column_value); end loop; CLOSE c; END;