Oracle 18c

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

CREATE TYPE string_list IS TABLE OF VARCHAR2(50);
CREATE TABLE synonyms (
  id          NUMBER(20,0)
              GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY,
  word        VARCHAR(50)
              NOT NULL,
  replacement VARCHAR(50)
              NOT NULL,
  UNIQUE ( replacement )
);
INSERT INTO synonyms ( word, replacement )
SELECT 'MOUNTAIN',      'MOUNT'         FROM DUAL UNION ALL
SELECT 'MOUNTAIN',      'MOUNTAIN'      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 UNION ALL
SELECT 'EDUCATION',     'EDUCATION'     FROM DUAL UNION ALL
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 'FOOD',          'FOOD'          FROM DUAL UNION ALL
SELECT 'FOOD',          'BAR'           FROM DUAL UNION ALL
SELECT 'FOOD',          'BISTRO'        FROM DUAL UNION ALL
SELECT 'FOOD',          'BREAKFAST'     FROM DUAL UNION ALL
SELECT 'FOOD',          'BUFFET'        FROM DUAL UNION ALL
SELECT 'FOOD',          'CABARET'       FROM DUAL UNION ALL
SELECT 'FOOD',          'CAFE'          FROM DUAL UNION ALL
SELECT 'FOOD',          'RESTAURANT'    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;
29 rows affected

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

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN string_list DETERMINISTIC
AS
  p_result       string_list := string_list();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/
create or replace function replace_names(
  i_sentence IN VARCHAR2
) return string_list
IS
  p_words    string_list := split_string( i_sentence, ' ' );
  p_replaced string_list;
BEGIN
  SELECT COALESCE( s.word, w.word )
  BULK COLLECT INTO p_replaced
  FROM   synonyms s
         RIGHT OUTER JOIN (
           SELECT ROWNUM AS rn,
                  COLUMN_VALUE AS word
           FROM   TABLE( p_words )
         ) w
         ON ( s.replacement = w.word )
  ORDER BY w.rn;
  
  RETURN p_replaced;
END;
/
select * from TABLE( replace_names('MOUNT VU FOOD GROWERS' ) );


COLUMN_VALUE


MOUNTAIN

VIEW

FOOD

GROWERS


SELECT name,
       column_value corrected_name
FROM   institutions i
       CROSS JOIN TABLE(replace_names(i.name));


NAME
CORRECTED_NAME


FORMULA VIEW UNIVERSITY
FORMULA

FORMULA VIEW UNIVERSITY
VIEW

FORMULA VIEW UNIVERSITY
EDUCATION

FOURMULA VULCAN COLLEGE
FORMULA

FOURMULA VULCAN COLLEGE
VULCAN

FOURMULA VULCAN COLLEGE
EDUCATION

MOUNTAIN VIEW RESTAURANT GROWERS
MOUNTAIN

MOUNTAIN VIEW RESTAURANT GROWERS
VIEW

MOUNTAIN VIEW RESTAURANT GROWERS
FOOD

MOUNTAIN VIEW RESTAURANT GROWERS
GROWERS

FORMULA VU CAFE
FORMULA

FORMULA VU CAFE
VIEW

FORMULA VU CAFE
FOOD


SELECT name,
       column_value AS corrected_name
FROM   institutions i
       CROSS JOIN TABLE( replace_names( i.name ) )
WHERE  column_value MEMBER OF replace_names('MOUNTAIN VU FOOD'); 


NAME
CORRECTED_NAME


FORMULA VIEW UNIVERSITY
VIEW

MOUNTAIN VIEW RESTAURANT GROWERS
MOUNTAIN

MOUNTAIN VIEW RESTAURANT GROWERS
VIEW

MOUNTAIN VIEW RESTAURANT GROWERS
FOOD

FORMULA VU CAFE
VIEW

FORMULA VU CAFE
FOOD


SELECT name,
       ( SELECT LISTAGG( column_value, ' ' ) WITHIN GROUP ( ORDER BY ROWNUM )
         FROM   TABLE( corrected_words )
       ) AS corrected_name,
       num_matches
FROM   (
  SELECT name,
         replace_names( name ) AS corrected_words,
         CARDINALITY( replace_names( name ) MULTISET INTERSECT replace_names('MOUNTAIN VU FOOD') ) AS num_matches
  FROM   institutions
)
WHERE  num_matches > 0
ORDER BY num_matches DESC; 


NAME
CORRECTED_NAME
NUM_MATCHES


MOUNTAIN VIEW RESTAURANT GROWERS
MOUNTAIN VIEW FOOD GROWERS
3

FORMULA VU CAFE
FORMULA VIEW FOOD
2

FORMULA VIEW UNIVERSITY
FORMULA VIEW EDUCATION
1

NAME	CORRECTED_NAME
FORMULA VIEW UNIVERSITY	FORMULA
FORMULA VIEW UNIVERSITY	VIEW
FORMULA VIEW UNIVERSITY	EDUCATION
FOURMULA VULCAN COLLEGE	FORMULA
FOURMULA VULCAN COLLEGE	VULCAN
FOURMULA VULCAN COLLEGE	EDUCATION
MOUNTAIN VIEW RESTAURANT GROWERS	MOUNTAIN
MOUNTAIN VIEW RESTAURANT GROWERS	VIEW
MOUNTAIN VIEW RESTAURANT GROWERS	FOOD
MOUNTAIN VIEW RESTAURANT GROWERS	GROWERS
FORMULA VU CAFE	FORMULA
FORMULA VU CAFE	VIEW
FORMULA VU CAFE	FOOD

NAME	CORRECTED_NAME	NUM_MATCHES
MOUNTAIN VIEW RESTAURANT GROWERS	MOUNTAIN VIEW FOOD GROWERS	3
FORMULA VU CAFE	FORMULA VIEW FOOD	2
FORMULA VIEW UNIVERSITY	FORMULA VIEW EDUCATION	1