Geek Answers Handbook

Split a row into Oracle SQL rows

After searching the forums, I came up with the following, but it doesn't work: /

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 are about 20,000 lines, my other option is to write a stored procedure for this ... Basically I need to split the lines so that for each of them there is a line:

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

etc.

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

+4
source share
3 answers

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 section. This is the case with multiple spaces.

If you have a predictable upper bound on the number of elements in a row, it might be worth comparing the results 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

+5
source

A more flexible and better solution that:

  • It does not depend on the predictability of the number of elements in the row.
  • independent of the identifier column, the solution gives the correct result regardless of the number of columns.
  • not even dependent on the DISTINCT keyword.

There are other examples using XMLTABLE and the MODEL clause , please read Separate the comma of a row in a table .

For instance,

Without column ID:

 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 the Identifier 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>
+3
source 
 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
-1
source

More articles:


All Articles