Разделение строки на несколько строк в Oracle 8i

У меня есть таблица, состоящая из списка имен, разделенных запятыми. Моя цель - разделить их по комнатам.

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

Моя цель:

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

Я прочитал несколько решений о том, как разбить строки на строки, но есть ли альтернативы для работы в Oracle 8i. Я следил за некоторыми статьями, чтобы разбить их на такие строки:

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;

Затем я делаю ВЫБОР из своей таблицы, как показано ниже:

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

и получил результат ниже, но не могу вывести значения для столбца Room:

Name  
Anne  
Amy  
Ben  
Bryan

Есть ли способ разбить на строки в Oracle 8i?


sql oracle oracle8i
person abigal689    schedule 03.02.2016    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Вы можете попробовать немного другой подход, без функций, с помощью иерархические запросы и функции обработки строк. Немного сложно, но это должно сработать:

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
person Aleksej    schedule 03.02.2016
comment
Привет, Алексей, я тоже пробовал этот способ. но продолжайте получать цикл CONNECT BY в пользовательских данных, и я еще не понял эту ошибку. - person abigal689; 03.02.2016
comment
Может быть, вы его как-то модифицировали? Только что протестировал в 11g, работает, но не могу попробовать на 8i. Если тот же код не работает для вас, я использовал кое-что, что не поддерживается в 8i, даже если все функции, которые я использовал, можно использовать в 8i. - person Aleksej; 03.02.2016

arrow_upward
0
arrow_downward

Я не уверен, поддерживается ли это в 8i, я тестировал это в 12c, и он работает нормально:

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

Таблица test создана

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

INSERT INTO тест прошел успешно 1 строка затронута

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

INSERT INTO тест прошел успешно 1 строка затронута

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

INSERT INTO тест прошел успешно 1 строка затронута

Использование XMLTABLE:

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

КОМНАТА | НАЗВАНИЯ

room1 | Энн

room2 | Эми

room2 | Шелдон

room3 | пенни

room3 | Леонард

person krishna chaitanya Pullakandam    schedule 03.02.2016
comment
вы можете найти другие способы достижения своей цели здесь: stackoverflow.com/questions/14328621/ - person krishna chaitanya Pullakandam; 03.02.2016
comment
XmlTable поддерживается только в версиях 10g и выше; связанное решение использует regexp_substr, даже не поддерживается в 8i - person Aleksej; 03.02.2016
comment
привет Кришна, Алексей, спасибо за информацию. Я попробую без использования connect by или regexp. - person abigal689; 03.02.2016

arrow_upward
0
arrow_downward

Вот альтернатива без использования подключения по

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
)
;
person Ricardo Arnold    schedule 03.02.2016