Oracle 11g Release 2

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

CREATE TYPE wordlist IS TABLE OF VARCHAR2(30);
CREATE TYPE word_synonyms IS OBJECT(
  word     VARCHAR2(30),
  synonyms wordlist
);
CREATE TYPE word_synonyms_list IS TABLE OF word_synonyms;
CREATE FUNCTION replace_synonyms(
  text         IN VARCHAR2,
  synonym_list IN word_synonyms_list
) RETURN VARCHAR2 DETERMINISTIC
IS
  p_text VARCHAR2(4000) := text;
BEGIN
  FOR i IN 1 .. synonym_list.COUNT LOOP
    FOR j IN 1 .. synonym_list(i).synonyms.COUNT LOOP
      p_text := REGEXP_REPLACE(
                  p_text,
                  '(^|[^a-z0-9])' || synonym_list(i).synonyms(j) || '($|[^a-z0-9])',
                  '\1' || synonym_list(i).word || '\2',
                  1,
                  0,
                  'i'
                );
      -- Handle repeated synonyms.
      p_text := REGEXP_REPLACE(
                  p_text,
                  '(^|[^a-z0-9])' || synonym_list(i).synonyms(j) || '($|[^a-z0-9])',
                  '\1' || synonym_list(i).word || '\2',
                  1,
                  0,
                  'i'
                );
    END LOOP;
  END LOOP;
  RETURN p_text;
END;
/
CREATE TABLE test_data ( text ) AS
SELECT 'FORMULA VIEW UNIVERSITY'    FROM DUAL UNION ALL
SELECT '4MULA VIEW UNIVERSITY'      FROM DUAL UNION ALL
SELECT 'FOURMULA VIEW UNIVERSITY'   FROM DUAL UNION ALL
SELECT 'FORMULA VU UNIVERSITY'      FROM DUAL UNION ALL
SELECT '4MULA VU UNIVERSITY'        FROM DUAL UNION ALL
SELECT 'FOURMULA VU UNIVERSITY'     FROM DUAL UNION ALL
SELECT 'FOURMULA VU SCHOOL'         FROM DUAL UNION ALL
SELECT 'FOURMULAE VULCAN SCHOOLING' FROM DUAL;
8 rows affected

SELECT text,
       replace_synonyms(
         text,
         word_synonyms_list(
           word_synonyms( '4MULA',      wordlist( 'formula', 'fourmula' ) ),
           word_synonyms( 'VIEW',       wordlist( 'vu' ) ),
           word_synonyms( 'UNIVERSITY', wordlist( 'school' ) )
         )
       ) AS substitutions
FROM   test_data


TEXT
SUBSTITUTIONS


FORMULA VIEW UNIVERSITY
4MULA VIEW UNIVERSITY

4MULA VIEW UNIVERSITY
4MULA VIEW UNIVERSITY

FOURMULA VIEW UNIVERSITY
4MULA VIEW UNIVERSITY

FORMULA VU UNIVERSITY
4MULA VIEW UNIVERSITY

4MULA VU UNIVERSITY
4MULA VIEW UNIVERSITY

FOURMULA VU UNIVERSITY
4MULA VIEW UNIVERSITY

FOURMULA VU SCHOOL
4MULA VIEW UNIVERSITY

FOURMULAE VULCAN SCHOOLING
FOURMULAE VULCAN SCHOOLING


SELECT text
FROM   test_data
WHERE  replace_synonyms(
         text,
         word_synonyms_list(
           word_synonyms( '4MULA',      wordlist( 'formula', 'fourmula' ) ),
           word_synonyms( 'VIEW',       wordlist( 'vu' ) ),
           word_synonyms( 'UNIVERSITY', wordlist( 'school' ) )
         )
       )
       LIKE '%4MULA VIEW%%UNIVERSITY%'


TEXT


FORMULA VIEW UNIVERSITY

4MULA VIEW UNIVERSITY

FOURMULA VIEW UNIVERSITY

FORMULA VU UNIVERSITY

4MULA VU UNIVERSITY

FOURMULA VU UNIVERSITY

FOURMULA VU SCHOOL

TEXT	SUBSTITUTIONS
FORMULA VIEW UNIVERSITY	4MULA VIEW UNIVERSITY
4MULA VIEW UNIVERSITY	4MULA VIEW UNIVERSITY
FOURMULA VIEW UNIVERSITY	4MULA VIEW UNIVERSITY
FORMULA VU UNIVERSITY	4MULA VIEW UNIVERSITY
4MULA VU UNIVERSITY	4MULA VIEW UNIVERSITY
FOURMULA VU UNIVERSITY	4MULA VIEW UNIVERSITY
FOURMULA VU SCHOOL	4MULA VIEW UNIVERSITY
FOURMULAE VULCAN SCHOOLING	FOURMULAE VULCAN SCHOOLING