Split String into rows Oracle SQL

Question

After searching the forums I have come up with the following but its not working :/

I have a table with the following;

ID |   Strings     
123|   abc fgh dwd   
243|   dfs dfd dfg  
353|   dfs  
424|   dfd dfw  
523|    
.  
.  
. 

Please not that there is around 20,000 rows my other option is to write a stored procedure to do this ...Basically I need to split the strings up so there is a row for each one like this

ID |  Strings  
123| abc  
123| fgh  
123| dwd  
243| dfs  

and so on...

this is what I have.

create table Temp AS   
SELECT ID, strings   
From mytable;  

SELECT DISTINCT ID, trim(regexp_substr(str, '[^ ]+', 1, level)) str  
FROM (SELECT ID, strings str FROM temp) t  
CONNECT BY instr(str, ' ', 1, level -1) >0  
ORDER BY ID;  

Any help is appreciated

Answer 1

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

Answer 2

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>