Splitting data in one column and updating new column with split data

Question

Hello all,

I'm working on Oracle 10g.One of the column's of my table stores data, as sampled below.

1722999340KK000200000

1444210829AB1001EX003

1444300000CD0148EX003

1722999340KL000200000

I want to split the data in the ratio of digits ( 4 ; 6; 6; 5) as shown below and store it in different columns

( A1 ||   A2   ||   A  3 || A4 )

1444 || 210829 || AB1001 || EX003

Appreciate if someone can give me some pointers on how to achieve the same.

Answer 1

I'm no expert when it comes to Oracle, but let's give it a shot. Assuming the original column is called ORIG, and the original data is some string type:

update <tablename> set A1 = substr(ORIG, 0, 4),
                       A2 = substr(ORIG, 4, 6),
                       A3 = substr(ORIG, 10, 6),
                       A4 = substr(ORIG, 16, 5);

Hope this helps

Tom