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

Question

How does the following query work, for this example ?

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

The query was found at https://stackoverflow.com/a/13716501

Another question how efficient this query is for example 3000 comma separated elements?

Answer 1

The first portion simply groups the string into 4 sets and returns each set. By adding a connect by, the OP is saying I want each set returned as it's own row limiting the number of rows returned to be equal to the sets of data in the original.

Breaking it down:

• '1,2,3' is the set we're playing with which contains subsets.
• '[^,]+ says ignore the comma's in the sets use it as a deliniator.
• ,1 says start at postion 1 in the substring
• level says return this numbered set from the sub set if a 2 were entered it would return the 2nd set.

Now by adding connect by, the author is breaking each set into its own row matching the row where it is in the original set.

I'd look up how REGEXP_SUBSTR works.

REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern. It is also similar to REGEXP_INSTR, but instead of returning the position of the substring, it returns the substring itself. This function is useful if you need the contents of a match string but not its position in the source string. The function returns the string as VARCHAR2 or CLOB data in the same character set as source_char.

Then understand how connect by works

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:

Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.

Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees,

Long and short play around with it, you'll learn a lot more that way.