add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.14
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
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;
1 rows affected
SELECT text FROM test_data WHERE text LIKE replace_synonyms( '%4MULA VU%%SCHOOL%', word_synonyms_list( word_synonyms( 'FORMULA', wordlist( '4mula', 'fourmula' ) ), word_synonyms( 'VIEW', wordlist( 'vu' ) ), word_synonyms( 'UNIVERSITY', wordlist( 'school' ) ) ) )
TEXT
FORMULA VIEW UNIVERSITY