Splitting two interdependent strings into rows

Question

I have two comma-separated strings like this:

'number1,number2,number3'
'word1, word2, word3'

And I want to split them into rows keeping the order of values. The expected result is:

COL1               COL2
-------------------------
number1          word1
number2          word2
number3          word3

There is a special condition: we can't use square brackets signs - [...].

PS. I've found how to split a single string using XMLTable, but I don't know how to join it with the second string's values keeping the same order.

Answer 1

I don't think you need to worry about the order as far as the rows are fixed and the string has the substr values in the same order.

Update OP wants to stick to XMLTABLE solution

Using XMLTABLE

SQL> WITH input_data AS
  2    (SELECT 'number1,number2,number3' str1, 'word1, word2, word3' str2 FROM dual
  3    ) ,
  4    temp_data1 AS
  5    (SELECT rownum rn,
  6      trim(COLUMN_VALUE) str1
  7    FROM input_data,
  8      xmltable(('"'
  9      || REPLACE(str1, ',', '","')
 10      || '"'))
 11    ) ,
 12    temp_data2 AS
 13    (SELECT rownum rn,
 14      trim(COLUMN_VALUE) str2
 15    FROM input_data,
 16      xmltable(('"'
 17      || REPLACE(str2, ',', '","')
 18      || '"'))
 19    )
 20  SELECT t1.str1,
 21    t2.str2
 22  FROM temp_data1 t1,
 23    temp_data2 t2
 24  WHERE t1.rn = t2.rn
 25  /

STR1    STR2
------- -------
number1 word1
number2 word2
number3 word3

SQL>

Using REGEXP_SUBSTR

SQL> WITH DATA AS
  2    ( SELECT 'number1,number2,number3' str1, 'word1, word2, word3' str2 FROM dual
  3    )
  4  SELECT trim(regexp_substr(str1, '[^,]+', 1, LEVEL)) str1,
  5    trim(regexp_substr(str2, '[^,]+', 1, LEVEL)) str2
  6  FROM DATA
  7    CONNECT BY LEVEL <= regexp_count(str1, ',')+1
  8  /

STR1                    STR2
----------------------- -------------------
number1                 word1
number2                 word2
number3                 word3

SQL>

For more examples, see https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-using-oracle-sql/