CODEWITHSUNDEEP
sqlregexstringoracle-database
Grant McKinnon
Grant McKinnon

Reputation: 455

Split String into rows Oracle SQL

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

Upvotes: 6

Views: 41210

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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>

Upvotes: 8

Steve
Steve

Reputation: 551

With T As 
      (select 123 as id, 'abc fgh dwd' as strings from dual

      union

      select 243 as id, 'dfs dfd dfg' as strings from dual

      union 

      Select 353 As Id, 'dfs' As Strings From Dual

      union 

      select 424 as id, 'dfd dfw' as strings from dual


      )


select distinct id, REGEXP_SUBSTR (Replace(Strings, ' ', ','), '[^,]+', 1, level) as Strings
from t
Connect By Level <= Length(Regexp_Replace(Replace(Strings, ' ', ','),'[^,]*'))+1
order by id, strings;


**********OUTPUT*************
        ID STRINGS   
---------- -----------
   123 abc     
   123 dwd   
   123 fgh     
   243 dfd     
   243 dfg     
   243 dfs      
   353 dfs      
   424 dfd     
   424 dfw    

 9 rows selected

Upvotes: -1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

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

Upvotes: 10

Related Questions