CODEWITHSUNDEEP
sqlregexoracle-databasesplitmultiple-columns
rcorbellini
rcorbellini

Reputation: 1337

Split words in many columns sql

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

Upvotes: 0

Views: 1186

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

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

Upvotes: 3

MickJ
MickJ

Reputation: 2197

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.

Upvotes: 0

Related Questions