Oracle 18c

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

CREATE TABLE subs01 (
   word VARCHAR(50),
   subs_list VARCHAR(500)
)
INSERT INTO subs01 (word, subs_list)
--VALUES('mountain',('mountain, mount, mt, mtn'));
VALUES('MOUNTAIN',('MOUNTAIN, MOUNT, MT, MTN'));
1 rows affected

INSERT INTO subs01 (word, subs_list)
--VALUES('view',('view, vu'));
VALUES('VIEW',('VIEW, VU'))
1 rows affected

INSERT INTO subs01 (word, subs_list)
--VALUES('formula',('formula, 4mula, fourmula'));
VALUES('FORMULA',('FORMULA, 4MULA, FOURMULA'))
1 rows affected

SELECT * FROM subs01


WORD
SUBS_LIST


MOUNTAIN
MOUNTAIN, MOUNT, MT, MTN

VIEW
VIEW, VU

FORMULA
FORMULA, 4MULA, FOURMULA


CREATE TABLE syns01 (
   word VARCHAR(50),
   syn_list VARCHAR(500)
)
INSERT INTO syns01 (word, syn_list)
--VALUES('education',('school, university, college, training'))
VALUES('EDUCATION',('SCHOOL, UNIVERSITY, COLLEGE, TRAINING'));
1 rows affected

INSERT INTO syns01 (word, syn_list)
--VALUES('food',('bar, bistro, breakfast, buffet, cabaret, cafe, cantina, cappuccino, chai, coffee, commissary, cuisine, deli, dhaba, dine, diner, dining, eat, eater, eats, edible, espresso, expresso, food, galley, gastropub, grill, java, kitchen, latte, lounge, pizza, pizzeria, pub, publichouse, restaurant, roast, sandwich, snack, snax, socialhouse, steak, sub, sushi, takeout, taphouse, taverna, tea, tiffin, trattoria, treat, treatery'))
VALUES('FOOD',('SYN, BAR, BISTRO, BREAKFAST, BUFFET, CABARET, CAFE, CANTINA, CAPPUCCINO, CHAI, COFFEE, COMMISSARY, CUISINE, DELI, DHABA, DINE, DINER, DINING, EAT, EATER, EATS, EDIBLE, ESPRESSO, EXPRESSO, FOOD, GALLEY, GASTROPUB, GRILL, JAVA, KITCHEN, LATTE, LOUNGE, PIZZA, PIZZERIA, PUB, PUBLICHOUSE, RESTAURANT, ROAST, SANDWICH, SNACK, SNAX, SOCIALHOUSE, STEAK, SUB, SUSHI, TAKEOUT, TAPHOUSE, TAVERNA, TEA, TIFFIN, TRATTORIA, TREAT, TREATERY'))
1 rows affected

INSERT INTO syns01(word, syn_list)
--VALUES('store','convenience, food, grocer, grocery, market, mart, shop, store, variety')
VALUES('STORE',('SYN, CONVENIENCE, FOOD, GROCER, GROCERY, MARKET, MART, SHOP, STORE, VARIETY'));
1 rows affected

INSERT INTO syns01(word, syn_list)
--VALUES('refrigetarion','appliance, coldstorage, foodlocker, freeze, fridge, ice, refrigeration')
VALUES('REFRIGERATION',('APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION'))
1 rows affected

select * from syns01


WORD
SYN_LIST


EDUCATION
SCHOOL, UNIVERSITY, COLLEGE, TRAINING

FOOD
SYN, BAR, BISTRO, BREAKFAST, BUFFET, CABARET, CAFE, CANTINA, CAPPUCCINO, CHAI, COFFEE, COMMISSARY, CUISINE, DELI, DHABA, DINE, DINER, DINING, EAT, EATER, EATS, EDIBLE, ESPRESSO, EXPRESSO, FOOD, GALLEY, GASTROPUB, GRILL, JAVA, KITCHEN, LATTE, LOUNGE, PIZZA, PIZZERIA, PUB, PUBLICHOUSE, RESTAURANT, ROAST, SANDWICH, SNACK, SNAX, SOCIALHOUSE, STEAK, SUB, SUSHI, TAKEOUT, TAPHOUSE, TAVERNA, TEA, TIFFIN, TRATTORIA, TREAT, TREATERY

STORE
SYN, CONVENIENCE, FOOD, GROCER, GROCERY, MARKET, MART, SHOP, STORE, VARIETY

REFRIGERATION
APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION


CREATE TABLE institutions ( name ) AS
SELECT 'FORMULA VIEW UNIVERSITY'    FROM DUAL UNION ALL
SELECT 'FOURMULA VULCAN COLLEGE' FROM DUAL UNION ALL
SELECT 'MOUNTAIN VIEW RESTAURANT GROWERS' FROM DUAL UNION ALL 
SELECT 'FORMULA VU CAFE' FROM DUAL;
4 rows affected

SELECT * FROM institutions


NAME


FORMULA VIEW UNIVERSITY

FOURMULA VULCAN COLLEGE

MOUNTAIN VIEW RESTAURANT GROWERS

FORMULA VU CAFE


create type array_t is varray(100) of varchar2(4000);
/
create or replace function replace_names(i_name IN VARCHAR2, 
                                         i_field IN VARCHAR2) return array_t
is
  array array_t := array_t(); -- Initialise it
  p_name VARCHAR2(4000):= i_name;
  tmp VARCHAR2(4000);
  v_idx NUMBER:=0;
  v_flag BOOLEAN:= true;
  
  cursor c_name is
    select word, text, count(*) over (partition by text) as counter
    from(
         select word, trim(UPPER(column_value)) text 
         from subs01, xmltable(('"' ||replace(subs_list, ',', '","') || '"'))
         where regexp_like (i_name,'(^|\s)'||trim(column_value)||'(\s|$)','i')
         union 
         select word, trim(column_value) text 
         from syns01, xmltable(('"' ||replace(syn_list, ',', '","') || '"'))
         where regexp_like (i_name,'(^|\s)'||trim(column_value)||'(\s|$)','i')
    )
    order by counter asc;
  cursor c_field is
    select regexp_substr(i_field,'[^ ]+', 1, level) as descriptives 
    from dual
    connect by regexp_substr(i_field, '[^ ]+', 1, level) is not null;

BEGIN
   FOR i in c_name LOOP
      IF i.counter = 1 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER=1');
        DBMS_OUTPUT.PUT_LINE('p_name: '||p_name||' ,i.text: '||i.text||' ,i.word: '||UPPER(i.word));
        p_name := regexp_replace(
              p_name, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
              '\1' || UPPER(i.word) || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('p_name: '|| p_name);
      ELSE
        DBMS_OUTPUT.PUT_LINE('COUNTER>1');
        DBMS_OUTPUT.PUT_LINE('p_name: '||p_name||' ,i.text: '||i.text||' ,i.word: '||UPPER(i.word));
        v_idx:= v_idx+1;
        v_flag:= false;
		array.extend;
        array(v_idx):= regexp_replace(
                  p_name, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
                  '\1' || UPPER(i.word) || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('array(v_idx): '||array(v_idx)||' ,v_idx: '||v_idx);
      END IF;
   END LOOP;
   IF v_flag=true THEN
     v_idx:= v_idx+1;
	 array.extend;
     array(v_idx):=p_name;
   END IF;

   RETURN array;
   
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
   
END;
/
select replace_synonyms('MOUNT VU','FOOD GROWERS') from dual;
ORA-00904: "REPLACE_SYNONYMS": invalid identifier

SELECT name, column_value corrected_name
FROM institutions, TABLE(replace_synonyms(name))
ORA-00904: "REPLACE_SYNONYMS": invalid identifier

SELECT name, column_value corrected_name
FROM institutions, TABLE(replace_synonyms(name))
WHERE column_value IN (SELECT column_value FROM TABLE(replace_synonyms('MOUNTAIN VU FOOD'))); 
ORA-00904: "REPLACE_SYNONYMS": invalid identifier

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0

WORD	SUBS_LIST
MOUNTAIN	MOUNTAIN, MOUNT, MT, MTN
VIEW	VIEW, VU
FORMULA	FORMULA, 4MULA, FOURMULA

WORD	SYN_LIST
EDUCATION	SCHOOL, UNIVERSITY, COLLEGE, TRAINING
FOOD	SYN, BAR, BISTRO, BREAKFAST, BUFFET, CABARET, CAFE, CANTINA, CAPPUCCINO, CHAI, COFFEE, COMMISSARY, CUISINE, DELI, DHABA, DINE, DINER, DINING, EAT, EATER, EATS, EDIBLE, ESPRESSO, EXPRESSO, FOOD, GALLEY, GASTROPUB, GRILL, JAVA, KITCHEN, LATTE, LOUNGE, PIZZA, PIZZERIA, PUB, PUBLICHOUSE, RESTAURANT, ROAST, SANDWICH, SNACK, SNAX, SOCIALHOUSE, STEAK, SUB, SUSHI, TAKEOUT, TAPHOUSE, TAVERNA, TEA, TIFFIN, TRATTORIA, TREAT, TREATERY
STORE	SYN, CONVENIENCE, FOOD, GROCER, GROCERY, MARKET, MART, SHOP, STORE, VARIETY
REFRIGERATION	APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION