Hi Tom,
Hope your doing good!

The below query returns the right output, but my question is as far as my understanding here in the first check itself i.e. (Level-1) the condition fails(1-1). but I dont know how does it proceeds further,can you explain how does connect by level works in this scenario.

WITH DATA AS
( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
)
SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str,level
FROM DATA
CONNECT BY instr(str, ',', 1, LEVEL-1) > 0;


output
STR LEVEL
---------------------------------------- ----------
word1 1
word2 2
word3 3
word4 4
word5 5
word6 6

6 rows selected

If I put only level and level+1 as below the output is decreased am really confused about how it works. Please explain me along with its working flow in detail.

WITH DATA AS
( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
)
SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str,level
FROM DATA
CONNECT BY instr(str, ',', 1, LEVEL) > 0;


output
STR LEVEL
---------------------------------------- ----------
word1 1
word2 2
word3 3
word4 4
word5 5

WITH DATA AS
( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
)
SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str,level
FROM DATA
CONNECT BY instr(str, ',', 1, LEVEL+1) > 0;


output
STR LEVEL
---------------------------------------- ----------
word1 1
word2 2
word3 3
word4 4

Kindly explain me how it works in detail. Thanks in advance.