Split words in many columns sql

Question

I have a table column with these values:

Column-desc
------------------
"car broken"
"motorcycle broken"
"motorcycle clean"
"motorcycle clean and broken"
------------------

and I need to make a select that returns this:

return-desc1 | return-desc2 | return-desc3 |return-desc4 
------------------------------------------------------
car          |  broken      |              |
motorcycle   |  broken      |              |
motorcycle   |  clean       |              |
motorcycle   |  clean       | and          | broken
-------------------------------------------------------

(yes, max ll return 4 desc). Could I use a regex for this case?

If this helps: I am using Oracle 10g

this might get you going in the right direction: http://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values — Kyle Renfro, Mar 13 '13 at 13:50

score 3 · Accepted Answer · answered Mar 13 '13 at 13:55

3

select
    regexp_substr("Column-desc", '\S+', 1, 1) as "return-desc1",
    regexp_substr("Column-desc", '\S+', 1, 2) as "return-desc2",
    regexp_substr("Column-desc", '\S+', 1, 3) as "return-desc3",
    regexp_substr("Column-desc", '\S+', 1, 4) as "return-desc4"
from your_table

answered Mar 13 '13 at 13:55

Egor Skriptunoff

23,359
2
34
64

perfect this is a perfect answers! – rcorbellini Mar 13 '13 at 13:58

score 0 · Answer 2 · answered Mar 13 '13 at 13:48

0

Honestly my suggestion would be to get the column-desc and do the split work on the programming layer. It would be easier and probably more optimal that way.

answered Mar 13 '13 at 13:48

MickJ

2,127
3
20
34

Split words in many columns sql

2 Answers2