REGEXP_REPLACE
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The REGEXP_REPLACE()
function searches a string for a regular expression pattern, and replaces all or the first occurrence of that string.
Vendors offer different versions of this function, so jOOQ standardises them as two synthetic functions:
-
REGEXP_REPLACE_ALL()
-
REGEXP_REPLACE_FIRST()
For example:
SELECT regexp_replace_all('hello', 'l', ''), regexp_replace_first('hello', 'l', '');
create.select( regexpReplaceAll(val("hello"), "l", ""), regexpReplaceFirst(val("hello"), "l", "")).fetch();
The result being
+--------------------+----------------------+ | regexp_replace_all | regexp_replace_first | +--------------------+----------------------+ | heo | helo | +--------------------+----------------------+
Dialect support
This example using jOOQ:
regexpReplaceAll(val("hello"), "l", "")
Translates to the following dialect specific expressions:
Aurora MySQL, DB2, H2, HSQLDB, MariaDB, MySQL, Oracle, Teradata, Vertica
regexp_replace('hello', 'l', '')
Aurora Postgres, CockroachDB, MemSQL, Postgres, YugabyteDB
regexp_replace('hello', 'l', '', 'g')
ClickHouse
replaceRegexpAll('hello', 'l', '')
Hana
replace_regexpr('l' IN 'hello' WITH '')
Informix
regex_replace('hello', 'l', '')
ASE, Access, BigQuery, Derby, DuckDB, Exasol, Firebird, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!