Split String into rows Oracle SQL

This should do the trick:

SELECT DISTINCT ID, regexp_substr("Strings", '[^ ]+', 1, LEVEL)
FROM T
CONNECT BY regexp_substr("Strings", '[^ ]+', 1, LEVEL) IS NOT NULL
ORDER BY ID;

Notice how I used regexp_substr in the connect by clause too. This is to deal with the case of multiple spaces.

If you have a predictable upper bound on the number of items per line, it might worth comparing the performances of the recursive query above with a simple CROSS JOIN:

WITH N as (SELECT LEVEL POS FROM DUAL CONNECT BY LEVEL < 10)
--                                                       ^^
--                                                 up to 10 substrings
SELECT ID, regexp_substr("Strings", '[^ ]+', 1, POS)
FROM T CROSS JOIN N
WHERE regexp_substr("Strings", '[^ ]+', 1, POS) IS NOT NULL
ORDER BY ID;

See http://sqlfiddle.com/#!4/444e3/1 for a live demo

A more flexible and better solution which:

doesn't depend upon the predictability of the number of items per line.
doesn't depend on the ID column, the solution gives correct result irrespective of the number of column.
doesn't even depend upon the DISTINCT keyword.

There are other examples using XMLTABLE and MODEL clause, please read Split comma delimited strings in a table.

For example,

Without ID column:

SQL> WITH T AS
  2    (SELECT 'abc fgh dwd' AS text FROM dual
  3    UNION
  4    SELECT 'dfs dfd dfg' AS text FROM dual
  5    UNION
  6    SELECT 'dfs' AS text FROM Dual
  7    UNION
  8    SELECT 'dfd dfw' AS text FROM dual
  9    )
 10  SELECT trim(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
 11  FROM t,
 12    TABLE (CAST (MULTISET
 13    (SELECT LEVEL FROM dual CONNECT BY instr(t.text, ' ', 1, LEVEL - 1) > 0
 14    ) AS sys.odciNumberList )) lines
 15  /

TEXT
-----------
abc
fgh
dwd
dfd
dfw
dfs
dfs
dfd
dfg

9 rows selected.

With ID column:

SQL> WITH T AS
  2    (SELECT 123 AS id, 'abc fgh dwd' AS text FROM dual
  3    UNION
  4    SELECT 243 AS id, 'dfs dfd dfg' AS text FROM dual
  5    UNION
  6    SELECT 353 AS Id, 'dfs' AS text FROM Dual
  7    UNION
  8    SELECT 424 AS id, 'dfd dfw' AS text FROM dual
  9    )
 10  SELECT id, trim(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
 11  FROM t,
 12    TABLE (CAST (MULTISET
 13    (SELECT LEVEL FROM dual CONNECT BY instr(t.text, ' ', 1, LEVEL - 1) > 0
 14    ) AS sys.odciNumberList )) lines
 15   ORDER BY id
 16   /

        ID TEXT
---------- -----------
       123 abc
       123 fgh
       123 dwd
       243 dfs
       243 dfd
       243 dfg
       353 dfs
       424 dfd
       424 dfw

9 rows selected.

SQL>

Split String into rows Oracle SQL

Related

Recent Posts