Oracle – Divide a string based on total length of string

I have a non standardized data in one of the column and I need to be splitting the data into 3 parts. there is no specific requirement on length each column should have. It’s just that the entire data to be split into 3

Input Data 01 : test , test ,test/test Input data 02: Test; test,testtesting123data123datadatatawerr

OutPut 01: Col1=test Col2= test,test Col3=/test Output 02: Col1= Test; col2= test,test col3=testing123data123datadatatawerr

Is there a way to take the total length and based on that divide into 3 parts. Concatenating of split data I need to get the entire string back.

Answer

I need to be splitting the data into 3 parts. there is no specific requirement on length each column should have

The simplest approach is to use substr():

select
    substr(col, 1, 1) col1,
    substr(col, 2, 1) col2
    substr(col, 3)    col3
from mytable

The first two columns contain one character each, starting from the beginning of the string; the last column contains the reminder. This guarantees that each column will be fed (provided that the string is at least 3 characters).

On the other hand if you want to split in three parts whose length is close to equal, you can do:

select
    substr(col, 1, round(length(col) / 3)) col1,
    substr(col, 1 + round(length(col) / 3), round(length(col) / 3)) col2
    substr(col, 1 + 2 * round(length(col) / 3))    col3
from mytable