Oracle SQL Split string

Example of data sample

Second, split single line

SELECTId_,regexp_substr (Name_,'[^,]+',1, rownum) H2 from(SelectId_,name_ fromTest_reg_count TwhereT.id_=2) CONNECT by  Level <=Length (name_)-LengthReplace(Name_,',',"'))+1;--orSELECTId_,regexp_substr (Name_,'[^,]+',1, rownum) H2 from(SelectId_,name_ fromTest_reg_count TwhereT.id_=2) CONNECT byRegexp_substr (Name_,'[^,]+',1, rownum) is  not NULL;

Three, split multiple lines

SELECTId_,regexp_substr (Name_,'[^,]+',1, Level) name_ fromTest_reg_count CONNECT by  Level <=Length (name_)-LengthReplace(Name_,',',"'))+1                 andName_=PRIOR name_ andPRIOR Dbms_random.value<1; --orSELECTId_,regexp_substr (Name_,'[^,]+',1, Level) name_ fromTest_reg_count CONNECT by  Level <=Length (name_)-LengthReplace(Name_,',',"'))+1                 andName_=PRIOR name_ andPRIOR Sys_guid () is  not NULL;

The most important here is the prior operator, which describes the official documentation of Oracle for prior:

In a hierarchical query, one expression in the  CONNECT   by   condition  must be qualified by the  PRIOR   operator. If the  CONNECT   by   condition  is Compound, then only one condition requires the  PRIOR  operator, although You can have multiple  PRIOR  conditions.  PRIOR  evaluates the Immediately following expression for the parent row of the current row in a hierarchical query.

　　PRIORis most commonly used when comparing column values with the equality operator. (The PRIOR keyword can is on either side of the operator.) PRIOR Causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) is theoretically possible in CONNECT BY clauses. However, the conditions created by these and other operators can result in an infinite loop through the possible combinations. In this case, Oracle detects the loop at run time and returns an error.

Oracle SQL Split string