Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE subs (
   word VARCHAR(50),
   subs_list VARCHAR(500)
)
INSERT INTO subs (word, subs_list)
--VALUES('mountain',('mountain, mount, mt, mtn'));
VALUES('MOUNTAIN',('MOUNTAIN, MOUNT, MT, MTN'));
1 rows affected

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

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

SELECT * FROM subs


WORD
SUBS_LIST


MOUNTAIN
MOUNTAIN, MOUNT, MT, MTN

VIEW
VIEW, VU

FORMULA
FORMULA, 4MULA, FOURMULA


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

INSERT INTO syns (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 syns(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 syns(word, syn_list)
--VALUES('refrigetarion','appliance, coldstorage, foodlocker, freeze, fridge, ice, refrigeration')
VALUES('REFRIGERATION',('SYN, APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION'))
1 rows affected

select * from syns


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
SYN, APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION


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

SELECT * FROM institutions


NAME


FORMULA VIEW UNIVERSITY GROWERS

FOURMULA VULCAN COLLEGE

4MULA VU CAFE GROWERS

MOUNT VU BAR GROWERS


--Working on local Oracle 11g R2, but not in Oracle 11gR2, just working on Oracle 18
SELECT word, text, count(*) over (partition by text) as counter
FROM(
    select word, trim(UPPER(column_value)) text 
    from subs, xmltable(('"' ||replace(subs_list, ',', '","') || '"'))
    where 'MOUNT VU SYN GROWERS' LIKE '%'||trim(column_value)||'%'
    union 
    select word, trim(column_value) text 
    from syns, xmltable(('"' ||replace(syn_list, ',', '","') || '"'))
    where 'MOUNT VU SYN GROWERS' like '%'||trim(column_value)||'%'
)
order by counter asc;


WORD
TEXT
COUNTER


MOUNTAIN
MOUNT
1

VIEW
VU
1

STORE
SYN
3

FOOD
SYN
3

REFRIGERATION
SYN
3


create type array_t is varray(100) of varchar2(4000);
/
create or replace function replace_synonyms(i_text in varchar2) return array_t
is
  array array_t := array_t(); -- Initialise it
  p_text VARCHAR2(4000):= i_text;
  tmp VARCHAR2(4000);
  v_idx NUMBER:=1;
  v_flag BOOLEAN:= true;
  
  cursor c_subs is
    select word, text, count(*) over (partition by text) as counter
    from(
         select word, trim(UPPER(column_value)) text 
         from subs, xmltable(('"' ||replace(subs_list, ',', '","') || '"'))
         where i_text LIKE '%'||trim(column_value)||'%'
         union 
         select word, trim(column_value) text 
         from syns, xmltable(('"' ||replace(syn_list, ',', '","') || '"'))
         where i_text like '%'||trim(column_value)||'%'
    )
    order by counter asc;
      
BEGIN
   FOR i in c_subs LOOP
      IF i.counter = 1 THEN
        DBMS_OUTPUT.PUT_LINE('COUNTER=1');
        DBMS_OUTPUT.PUT_LINE('p_text: '||p_text||' ,i.text: '||i.text||' ,i.word: '||i.word);
        p_text := regexp_replace(
              p_text, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
              '\1' || i.word || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('p_text: '|| p_text);
      ELSE
        DBMS_OUTPUT.PUT_LINE('COUNTER>1');
        DBMS_OUTPUT.PUT_LINE('p_text: '||p_text||' ,i.text: '||i.text||' ,i.word: '||i.word);
        array.extend;
        array(v_idx):= regexp_replace(
                  p_text, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
                  '\1' || i.word || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('array(v_idx): '||array(v_idx)||' ,v_idx: '||v_idx);
        v_idx:= v_idx+1;
        v_flag:= false;
      END IF;
   END LOOP;
   IF v_flag=true THEN
     array(v_idx):=p_text;
     v_idx:= v_idx+1;
   END IF;
   
   DBMS_OUTPUT.PUT_LINE('v_idx: '||v_idx);
   DBMS_OUTPUT.PUT_LINE('---------------------------------');
   dbms_output.put_line('Array Content: ');
   DBMS_OUTPUT.PUT_LINE(array(1));
   DBMS_OUTPUT.PUT_LINE(array(2));
   DBMS_OUTPUT.PUT_LINE(array(3));
   
   RETURN array;
   
END;
/
DECLARE
type array_t is varray(20) of varchar2(4000);
array array_t := array_t(); -- Initialise it

BEGIN
array.extend;
array(1):= regexp_replace(
                  'MOUNTAIN VIEW SYN GROWERS', '(^|[^a-z0-9])' || 'SYN' || '($|[^a-z0-9])', 
                  '\1' || 'STORE' || '\2', 1, 0,  'i');
DBMS_OUTPUT.PUT_LINE('array(1): '||array(1));
END;
/
1 rows affected

dbms_output:
array(1): MOUNTAIN VIEW STORE GROWERS

--PLSQL BLOCK same as function working for same name
DECLARE
  p_text VARCHAR2(4000):= 'MOUNT VU SYN GROWERS';
  tmp VARCHAR2(4000);
  type array_t is varray(20) of varchar2(4000);
  array array_t := array_t(); -- Initialise it
  v_idx NUMBER:=1;
  v_flag BOOLEAN:= true;
  
  cursor c_subs is
    select word, text, count(*) over (partition by text) as counter
    from(
         select word, trim(UPPER(column_value)) text 
         from subs, xmltable(('"' ||replace(subs_list, ',', '","') || '"'))
         where 'MOUNT VU SYN GROWERS' LIKE '%'||trim(column_value)||'%'
         union 
         select word, trim(column_value) text 
         from syns, xmltable(('"' ||replace(syn_list, ',', '","') || '"'))
         where 'MOUNT VU SYN GROWERS' like '%'||trim(column_value)||'%'
    )
    order by counter asc;
      
BEGIN
   FOR i in c_subs LOOP
      IF i.counter = 1 THEN
        p_text := regexp_replace(
              p_text, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
              '\1' || i.word || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('p_text: '|| p_text);
      ELSE
        array.extend;
        array(v_idx):= regexp_replace(
                  p_text, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
                  '\1' || i.word || '\2', 1, 0,  'i');
        DBMS_OUTPUT.PUT_LINE('array(v_idx): '||array(v_idx)||' ,v_idx: '||v_idx);
        v_idx:= v_idx+1;
        v_flag:= false;
      END IF;
   END LOOP;
   IF v_flag=true THEN
     array(v_idx):=p_text;
     v_idx:= v_idx+1;
   END IF;
   
   DBMS_OUTPUT.PUT_LINE('v_idx: '||v_idx);
   DBMS_OUTPUT.PUT_LINE('---------------------------------');
   dbms_output.put_line('Array Content: ');
   DBMS_OUTPUT.PUT_LINE(array(1));
   DBMS_OUTPUT.PUT_LINE(array(2));
   DBMS_OUTPUT.PUT_LINE(array(3));
END;
/
1 rows affected

dbms_output:
p_text: MOUNTAIN VU SYN GROWERS
p_text: MOUNTAIN VIEW SYN GROWERS
array(v_idx): MOUNTAIN VIEW STORE GROWERS ,v_idx: 1
array(v_idx): MOUNTAIN VIEW FOOD GROWERS ,v_idx: 2
array(v_idx): MOUNTAIN VIEW REFRIGERATION GROWERS ,v_idx: 3
v_idx: 4
---------------------------------
Array Content: 
MOUNTAIN VIEW STORE GROWERS
MOUNTAIN VIEW FOOD GROWERS
MOUNTAIN VIEW REFRIGERATION GROWERS

select replace_synonyms('MOUNT VU SYN GROWERS') from dual;
/*Error in local Oracle 11gR2:
SQL> SELECT replace_synonyms('MOUNT VU SYN GROWERS') from dual;
SELECT replace_synonyms('MOUNT VU SYN GROWERS') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.REPLACE_SYNONYMS", line 35
ORA-06512: at "SYSTEM.REPLACE_SYNONYMS", line 35

35:  array(v_idx):= regexp_replace(
                  p_text, '(^|[^a-z0-9])' || i.text || '($|[^a-z0-9])', 
                  '\1' || i.word || '\2', 1, 0,  'i');

 array(2):= regexp_replace(
                  'MOUNTAIN VIEW SYN GROWERS', '(^|[^a-z0-9])' || 'SYN' || '($|[^a-z0-9])', 
                  '\1' || 'STORE' || '\2', 1, 0,  'i');
                  
COUNTER=1
p_text: MOUNT VU SYN GROWERS ,i.text: MOUNT ,i.word: MOUNTAIN
p_text: MOUNTAIN VU SYN GROWERS
COUNTER=1
p_text: MOUNTAIN VU SYN GROWERS ,i.text: VU ,i.word: VIEW
p_text: MOUNTAIN VIEW SYN GROWERS
COUNTER>1
p_text: MOUNTAIN VIEW SYN GROWERS ,i.text: SYN ,i.word: STORE
SQL>
*/
ORA-00933: SQL command not properly ended

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	SYN, APPLIANCE, COLDSTORAGE, FOODLOCKER, FREEZE, FRIDGE, ICE, REFRIGERATION