SQL & PL/SQL

Hi All,

I'm trying to  separate a string into column and the delimiter is "," .

create table base_tab(id number,seq number,lines varchar2 (100));

insert into base_tab values (1,1,'a,b,c,d,e,f');

insert into base_tab values (1,2,'x,f,c,d,e,f');

insert into base_tab values (2,1,'p,b,c,f,e,f');

with convert_dat as

(SELECT  id,REGEXP_SUBSTR (lines, '[^,]+', 1, 1)    AS A_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 2)    AS B_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 3)    AS C_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 4)    AS D_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 5)    AS E_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 6)    AS F_1

FROM    base_tab where id =1 order by seq)

select * from convert_dat;

where I'm getting the proper output. But while value comes like  'l,m,,o,,f'  that time its causing problem. it will take the next value 'o' instead of null.

insert into base_tab values (1,1,'l,m,,o,,f');

again run the query:

with convert_dat as

(SELECT  id,REGEXP_SUBSTR (lines, '[^,]+', 1, 1)    AS A_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 2)    AS B_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 3)    AS C_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 4)    AS D_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 5)    AS E_1

,       REGEXP_SUBSTR (lines, '[^,]+', 1, 6)    AS F_1

FROM    base_tab

where id =1

order by seq)

select * from convert_dat;

Output:-

require output:-