Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table institution (name varchar2(100), field varchar2(100));
insert into institution (name, field)
select 'FORMULA VIEW', 'UNIVERSITY RESEARCH' from dual union all
select 'FOURMULA VULCAN', 'COLLEGE INSTITUTE' from dual union all
select 'MOUNT VU', 'FOOD GROWERS' from dual union all
select 'FORMULA VU', 'CAFE SHOP' from dual;
4 rows affected

create table word_grp (grp varchar2(100), word varchar2(100));
insert into word_grp (grp, word)
select 'EDUCATION', 'SCHOOL' from dual union all
select 'EDUCATION', 'UNIVERSITY' from dual union all
select 'EDUCATION', 'COLLEGE' from dual union all
select 'EDUCATION', 'TRAINING' from dual union all
select 'RESTAURANT', 'BAR' from dual union all
select 'RESTAURANT', 'BISTRO' from dual union all
select 'RESTAURANT', 'BREAKFAST' from dual union all
select 'RESTAURANT', 'BUFFET' from dual union all
select 'RESTAURANT', 'CABARET' from dual union all
select 'RESTAURANT', 'CAFE' from dual union all
select 'RESTAURANT', 'FOOD' from dual union all
select 'RESTAURANT', 'GALLEY' from dual union all
select 'RESTAURANT', 'GASTROPUB' from dual union all
select 'RESTAURANT', 'GRILL' from dual union all
select 'STORE', 'CONVENIENCE' from dual union all
select 'STORE', 'FOOD' from dual union all
select 'STORE', 'GROCER' from dual union all
select 'STORE', 'GROCERY' from dual union all
select 'STORE', 'MARKET' from dual union all
select 'STORE', 'MART' from dual union all
select 'STORE', 'SHOP' from dual union all
select 'STORE', 'STORE' from dual union all
select 'STORE', 'VARIETY' from dual union all
select 'REFRIGERATION', 'APPLIANCE' from dual union all
select 'REFRIGERATION', 'COLDSTORAGE' from dual union all
select 'REFRIGERATION', 'FOODLOCKER' from dual union all
select 'REFRIGERATION', 'FREEZE' from dual union all
select 'REFRIGERATION', 'FRIDGE' from dual union all
select 'REFRIGERATION', 'ICE' from dual union all
select 'REFRIGERATION', 'REFRIGERATION' from dual union all
select 'MOUNTAIN', 'MOUNTAIN' from dual union all
select 'MOUNTAIN', 'MOUNT' from dual union all
select 'MOUNTAIN', 'MT' from dual union all
select 'MOUNTAIN', 'MTN' from dual union all
select 'VIEW', 'VIEW' from dual union all
select 'VIEW', 'VU' from dual union all
select 'FORMULA', 'FORMULA' from dual union all
select 'FORMULA', '4MULA' from dual union all
select 'FORMULA', 'FOURMULA' from dual;
39 rows affected

create or replace function normstring(in_str varchar2) return varchar2 is
  i integer := 1;
  v_str varchar2(4000) := in_str;
  v_grp varchar2(100);
  v_word varchar2(100);
begin
  while regexp_substr(v_str, '\w+', 1, i) is not null loop
    v_word := regexp_substr(v_str, '\w+', 1, i);
    select max(grp) into v_grp from word_grp where word = v_word;
    if v_grp is not null then
      v_str := replace(v_str, v_word, v_grp);
    end if;
    i := i + 1;
  end loop;
  return v_str;
end normstring;
/
with desired as
(
  select '4MULA VU' as name, 'SCHOOL RESEARCH' as field from dual union all
  select '4MULA VU' as name, 'SCHOOL INSTITUTE' as field from dual union all
  select '4MULA VU' as name, 'BAR BECUE' as field from dual union all
  select 'MT VIEW' as name, 'GROCERY STORE' as field from dual union all
  select 'MOUNT EVEREST' as name, 'GROCERY STORE' as field from dual
)
select d.*, i.*,
  case when i.name is null then 'no'
       when normstring(i.field) = normstring(d.field) then 'full' else 'partly'
  end as match
from desired d
left join institution i on normstring(i.name) = normstring(d.name)
order by d.name, d.field, match;


NAME
FIELD
NAME
FIELD
MATCH


4MULA VU
BAR BECUE
FORMULA VIEW
UNIVERSITY RESEARCH
partly

4MULA VU
BAR BECUE
FORMULA VU
CAFE SHOP
partly

4MULA VU
SCHOOL INSTITUTE
FORMULA VIEW
UNIVERSITY RESEARCH
partly

4MULA VU
SCHOOL INSTITUTE
FORMULA VU
CAFE SHOP
partly

4MULA VU
SCHOOL RESEARCH
FORMULA VIEW
UNIVERSITY RESEARCH
full

4MULA VU
SCHOOL RESEARCH
FORMULA VU
CAFE SHOP
partly

MOUNT EVEREST
GROCERY STORE
null
null
no

MT VIEW
GROCERY STORE
MOUNT VU
FOOD GROWERS
partly

NAME	FIELD	NAME	FIELD	MATCH
4MULA VU	BAR BECUE	FORMULA VIEW	UNIVERSITY RESEARCH	partly
4MULA VU	BAR BECUE	FORMULA VU	CAFE SHOP	partly
4MULA VU	SCHOOL INSTITUTE	FORMULA VIEW	UNIVERSITY RESEARCH	partly
4MULA VU	SCHOOL INSTITUTE	FORMULA VU	CAFE SHOP	partly
4MULA VU	SCHOOL RESEARCH	FORMULA VIEW	UNIVERSITY RESEARCH	full
4MULA VU	SCHOOL RESEARCH	FORMULA VU	CAFE SHOP	partly
MOUNT EVEREST	GROCERY STORE	null	null	no
MT VIEW	GROCERY STORE	MOUNT VU	FOOD GROWERS	partly