Split string issues in following query

June 30, 2022

I have following query which inserts data into two columns in a table. If we notice, it actually creates only two rows instead of three rows, because I have specified only ‘2’ in 'CONNECT BY' clause. It clearly omits third values because of this. is there a way to change this behavior without changing its position. I am not able to specify CONNECT BY again in same query.AS My values are dynamic not able to change its position.

Expected output

Name Country
a     xy
c     yx
null  xc

Query tried

 INSERT INTO tbl_test_customer (
                NAME,
                COUNTRY
                
            )
                    SELECT
                    TRIM(regexp_substr('a,c', '[^,]+', 1, level)) str,
                     TRIM(regexp_substr('xy,yx,xc', '[^,]+', 1, level)) stri
                FROM
                    dual
                CONNECT BY 
                    instr('a,c', ',', 1, level - 1) > 0;

>Solution :

You don’t need two connect by clauses – just fix the one you have, so that it takes larger of two values:

SQL> SELECT
  2    TRIM(regexp_substr('a,c', '[^,]+', 1, level)) str,
  3    TRIM(regexp_substr('xy,yx,xc', '[^,]+', 1, level)) stri
  4  FROM dual
  5  CONNECT BY level <= greatest (regexp_count('a,c', ','),
  6                                regexp_count('xy,yx,xc', ',')
  7                               ) + 1;

STR          STRI
------------ --------------------------------
a            xy
c            yx
             xc

SQL>