Splitting string into multiple rows in Oracle 8i

Question

I have a table consisting a list of names separated by comma. My goal is to separated them by rooms.

Room  | Name  
room1 | Anne,Amy  
room2 | Ben,Bryan

My goal:

Room  | Name  
room1 | Anne   
room1 | Amy   
room2 | Ben  
room2 | Bryan  

I have read some solutions on how to split strings to rows, but are there alternatives to run on Oracle 8i. I have followed some articles to split them to rows like this:

create or replace function str2tbl( p_str IN varchar2 , p_delimiter in varchar2) return mytabletype
  as
    l_str      long default p_str || p_delimiter;
    l_n       number;
    l_data   mytabletype := mytabletype();
  begin
  loop
    l_n := instr( l_str, p_delimiter );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  end loop;
   return l_data;
  end str2tbl;

Then I do a SELECT from my table like below:

select * from the ( select cast(str2tbl( Name, ',' ) as mytableType ) 
from   SPLITSTRING);

and got below result, but cant bring out values for Room column:

Name  
Anne  
Amy  
Ben  
Bryan  

Is there any way to split to rows in Oracle 8i?

Answer 1

I am not sure if this is supported in 8i, I have tested this out in 12c, and it is working fine:

create table test (room varchar2(20), names varchar2(40));

Table 'test' created

insert into test values ('room1', 'anne');

INSERT INTO test successful 1 row affected

insert into test values ('room2', 'amy,sheldon');

INSERT INTO test successful 1 row affected

insert into test values ('room3', 'penny,leonard');

INSERT INTO test successful 1 row affected

Using XMLTABLE:

SELECT room,
trim(COLUMN_VALUE) names
FROM test,
xmltable(('"'
|| REPLACE(names, ',', '","')
|| '"'))
/

ROOM | NAMES

---

room1 | anne

room2 | amy

room2 | sheldon

room3 | penny

room3 | leonard

Answer 2

You can try with a slightly different approach, without functions, by the means of hierarchical queries and string manipulation functions. A bit tricky, but this should work:

with test (Room, Name) as  
(
 select 'room2', 'Ben,Bryan' from dual
)
select room, 
       trim (',' from  substr( name,
                               decode ( level, 
                                        1, 1,
                                           instr(name, ',', 1, level -1) +1
                                      ),
                               decode ( instr( name, ',', 1, level), 
                                        0, length(name),
                                           instr( name, ',', 1, level) - 
                                                decode ( level,
                                                         1, 1,
                                                            instr(name, ',', 1, level -1)
                                                       )
                                      )
                              )        
            ) as name
from test
connect by level = 1 or instr(name, ',', 1, level-1) != 0
order by 1

Answer 3

Here is an alternative without using connect by

drop table pivot_t;
drop table rooms;

create table rooms (Room varchar2(30), Persons varchar2(30));

insert into rooms values ('room1',  'Anne,Amy');
insert into rooms values ('room2',  'Ben,Bryan,Paul');
insert into rooms values ('room3',  'John,Michael,Nik,Patrick');

create table pivot_t(num integer);

begin
for i in 1..10000 loop
 insert into pivot_t values(i);
end loop;
end;
/

commit;



select 
room
,substr(Persons, start_pos, case
  when 
    next_comma - start_pos < 0 then 999
  else 
    next_comma - start_pos 
end)
from
(
select r.room 
      ,r.persons
      ,nvl(instr(r.Persons,',',1,decode(pt.num-1,0,null,pt.num-1) ),0) +1 START_POS
      ,instr(r.Persons,',',1,pt.num) NEXT_COMMA
  from rooms   r
      ,pivot_t pt
where length(r.Persons) - length(replace(r.Persons,',')) +1 >= pt.num
order by r.room, pt.num
)
;