Как заменить несколько слов в строке по таблице замен?

Question

Продолжение темы Как заменить в тексте токены значениями из таблицы замен

Есть таблица t1 с наименованиями организаций:

NAME
ЗАО РОМАШКА
РОМАШКА ЗАО
ООО ПУПКИ
АО КБ ОДИНБАНК
АО ГК ИППОДРОМ

Хочу убрать из названия организационные правовые формы и сокращения (АО, ЗАО, ГК и тд). Использую примерно следующую конструкцию:

select 
    NAME, 
    regexp_replace(NAME, '((^|\s|\W)ЗАО($|\s|\W))|((^|\s|\W)ООО($|\s|\W))|((^|\s|\W)АО($|\s|\W))|((^|\s|\W)ИП($|\s|\W))') as c_name 
from t1

Как проделать это, имея справочник замен t2

NAME	REPLACER
ЗАО	1
АО	2
ГК	3
...	...
КБ	49
ИП	50

Возможны 2 и более замен в одной записи. То есть, берем первую строку из t1 и по всем заменам из t2 прогоняем.

Подскажите, как это лучше реализовать?

Answer 1

with src as(
    select t1.id, t1.name, t2.name as repl,
           row_number() over(partition by t1.id order by NULL) rn,
           count(1) over(partition by t1.id) max_rn
      from t1, t2 where t1.name like '%' || t2.name || '%'
),
rcte(id, name, rn, max_rn) as(
    select id, regexp_replace(name, '((^|\s|\W)'|| repl ||'($|\s|\W))', '\2') name, rn, max_rn
      from src where rn=1
    union all
    select R.id, regexp_replace(R.name, '((^|\s|\W)'|| src.repl ||'($|\s|\W))', '\2'),
           R.rn+1, R.max_rn
      from rcte R, src where src.rn=R.rn+1 and src.id=R.id
)
select id, name
  from rcte
 where rn=max_rn

Можно как нибудь так. С помощью рекурсивного CTE применяем каждую новую замену к строке от предыдущей замены. Замечу, что над регуляркой надо поработать, потому что в вашем варианте она удаляла пробелы вокруг заменямой подстроки, что вело например к склеиванию 'АОИППОДРОМ' при удалении 'ГК'. Пока решил возвращать на место символ справа от замены. Но это то же не всегда корректно. Решите, что вы бы хотели видеть в таких ситуациях.

Answer 2

Для компенсации не очень производительного движка регуярных выражений, можно создать конвейерную функцию:

create or replace function getReplacedNames return strings pipelined is
    ret varchar2 (256); 
begin 
    for r in (
        select 
            row_number () over (partition by t1.name order by null) rn,   
            t1.name, t2.name token, t2.replacer 
        from t1 
        join t2 on regexp_like (t1.name, '((^|\W)'||t2.name||'($|\W))')
        order by t1.name, rn desc
    ) loop
        ret := regexp_replace (
            coalesce (ret , r.name), '(^|\W)'||r.token||'($|\W)', '\1'||r.replacer||'\2');
        if r.rn = 1 then pipe row (ret); ret := null; 
        end if;
    end loop;    
    return;
end;
/

Запрос и результат (подготовку схемы с тестовыми данными см. ниже):

select column_value name from table (getReplacedNames)
/

NAME
--------------------------------
2 3 ИППОДРОМ
2 ИППОДРОМ
2 49 ОДИНБАНК
1 РОМАШКА
РОМАШКА 1

То есть, берем первую строку из t1 и по всем заменам из t2 прогоняем.

Так как таблица t2 небольшая, то возможно, лучше так и сделать:

create or replace function getReplacedNames return strings pipelined is
    ret varchar2 (256);
    cursor c1 is select * from t1; 
    cursor c2 is select * from t2;
    type c2rows is table of c2%rowtype;
    r2 c2rows; 
begin 
    open c2;
    fetch c2 bulk collect into r2;  
    for r in c1 loop
        for i in 1..r2.count loop
            ret := regexp_replace (coalesce (ret , r.name), 
                '(^|\W)'||r2(i).name||'($|\W)', '\1'||r2(i).replacer||'\2');
        end loop;
        pipe row (ret); 
        ret := null; 
    end loop;    
    return;
end;
/

create table t1 (name) as
    select trim (column_value) from xmlTable ('
    "ЗАО РОМАШКА",
    "РОМАШКА ЗАО",
    "ООО ПУПКИ",
    "АО ИППОДРОМ", 
    "АО КБ ОДИНБАНК",
    "АО ГК ИППОДРОМ"') 
/
create table t2 (name, replacer) as
    select 'ЗАО',   '1'  from dual union all
    select 'АО',    '2'  from dual union all
    select 'ГК',    '3'  from dual union all
    select 'КБ',    '49' from dual union all
    select 'ИП',    '50' from dual
/
create or replace type strings is table of varchar2 (256)
/

БЛОГ НА HUSL

Как заменить несколько слов в строке по таблице замен?

Ответы (2 шт):