Splitting or Substr one column into multiple columns

Question

I have a table poll and i have 600 rows in poll. The type is CLOB. Some rows contain 12000 or 15000 character in data column. When it contain 12200 character i need make multi column like that:

data1        data2        data3        data4
-----------  -----------  -----------  -----------
first 4000   second 4000  third 4000   last 200

How can i do this?

user_10676716 · Answer

You can use dmbs_lob.substr():

select
    dbms_lob.substr(data, 4000,     1) data1,
    dbms_lob.substr(data, 4000,  4001) data2,
    dbms_lob.substr(data, 4000,  8001) data3,
    dbms_lob.substr(data, 4000, 12001) data4
from mytable

Splitting or Substr one column into multiple columns

1 Answers