CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Anom Pradonggo
Anom Pradonggo

Reputation: 1

Split columns in Oracle

i have this table :

id        text
-------   ----------------------------------------------------
128       1|abc def|2|asdfgh|qwerty|3|zxvcb|6/3/2015|2015|
128       1|asd adad|2|djfk|sdsd|3|asadd|7/3/2015|2015|
129       1|adads asad|2|saad|asda|3|asdad|8/3/2015|2015|

i want to be like this :

id      test_1             test_2                test_3
----    ------------------ --------------------- ------------------
128     1|abc def|         2|asdfgh|qwerty|      3|zxvcb|6/3/2015|2015|
128     1|asd adad|        2|djfk|sdsd|          3|asadd|7/3/2015|2015| 
129     1|adads asad|      2|saad|asda|          3|asdad|8/3/2015|2015|

Can any body help me please?

Upvotes: 0

Views: 60

Answers (1)

Rusty
Rusty

Reputation: 2138

select id, substr(text, pos1, pos2-pos1) as text1, substr(text, pos2, pos3-pos2) as text2, substr(text, pos3) as text3 
  from (select id, text, instr(text, '1|') pos1, instr(text, '2|') pos2, instr(text, '3|') pos3    
          from (
        select 128 as id, '1|abc def|2|asdfgh|qwerty|3|zxvcb|6/3/2015|2015|' as text from dual union all
        select 128 as id, '1|asd adad|2|djfk|sdsd|3|asadd|7/3/2015|2015|' as text from dual  union all
        select 129 as id, '1|adads asad|2|saad|asda|3|asdad|8/3/2015|2015|' as text from dual
        )
)

Upvotes: 1

Related Questions