Oracle Split () method for splitting data

-- ' One,two,three,four,five,six,seven,eight,n Ine,zero '  as source_string from dual), --counts the number of substrings in a string, using ', ' -- <= T2. Source_substring_count), -- ' [^,]+ ', 1 

Since the Regexp_count () method is new to Oracle 11g, the previous version does not, and here is another way to count the number of substrings:

--create a string to divide with T1 as (select' One,two,three,four,five,six,seven,eight,nine,zero 'as source_string from dual),--Count the number of substrings in a string--In the string ', ' the character is replaced with ', its reduced length is naturally the original string ', 'number of characters T2 as (select Length (t1.source_string)-Length (replace (t1.source_string, ', ', ')) + 1as Source_substring_count from T1),--creates an index column based on the number of substrings used to index the REGEXP_SUBSTR () method of T4 T3 as (select RowNum as Row_number from dual, T2 connect by row Num<=t2.source_substring_count),--intercepts the string T4 as (select T3.row_number as Substring_index, Regexp_substr (T1.source_string,) based on each index value' [^,]+ ', 1, T3.row_number) as substring from T1, T3) Select Substring_index, substring from T4; 

See a blogger wrote, just can use, first write down, at the same time thank the blogger

Original link: http://flforever1213.iteye.com/blog/1026096

Oracle Split () method for splitting data