sql - Connect by with regular expression. How does this query work? -

how following query work, example ?

select regexp_substr('1,2,3','[^,]+', 1, level) abc  dual  connect regex regexp_substr('1,2,3', '[^,]+', 1, level) not null 

the query found @ https://stackoverflow.com/a/13716501

another question how efficient query example 3000 comma separated elements?

the first portion groups string 4 sets , returns each set. adding connect by, op saying want each set returned it's own row limiting number of rows returned equal sets of data in original.

breaking down:

• '1,2,3' set we're playing contains subsets.
• '[^,]+ says ignore comma's in sets use deliniator.
• ,1 says start @ postion 1 in substring
• level says return numbered set sub set if 2 entered return 2nd set.

now adding connect by, author breaking each set own row matching row in original set.

i'd how regexp_substr works.

excerpt -- regexp_substr extends functionality of substr function letting search string regular expression pattern. similar regexp_instr, instead of returning position of substring, returns substring itself. function useful if need contents of match string not position in source string. function returns string varchar2 or clob data in same character set source_char.

end

then understand how connect by works

--excerpt --

oracle uses information these evaluations form hierarchy using following steps:

oracle selects root row(s) of hierarchy--those rows satisfy start condition.

oracle selects child rows of each root row. each child row must satisfy condition of connect condition respect 1 of root rows.

oracle selects successive generations of child rows. oracle first selects children of rows returned in step 2, , children of children, , on. oracle selects children evaluating connect condition respect current parent row.

if query contains clause without join, oracle eliminates rows hierarchy not satisfy condition of clause. oracle evaluates condition each row individually, rather removing children of row not satisfy condition.

oracle returns rows in order shown in figure 9-1. in diagram, children appear below parents. explanation of hierarchical trees,

long , short play around it, you'll learn alot more way.