A function to replace substrings based on regex patterns.
PostgreSQL function that replaces text matching a regex pattern with replacement text. Supports backreferences ($1, $2) to insert captured groups. Essential for complex text transformations that exceed LIKE capabilities.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0 | REGEXP_REPLACE() added in MySQL 8.0. |
| PostgreSQL | Native | 9.1 | |
| SQL Server | Not Supported | - | SQL Server does not support REGEXP_REPLACE. |
| Oracle | Native | 12c | |
| SQLite | Not Supported | - | SQLite does not support REGEXP_REPLACE. |
Details
This function is useful for text manipulation and data cleaning. However, support for it varies widely across SQL databases, with some lacking native regex replacement capabilities. Perhaps the ones that don't support it have played the 'I used regex to solve my problem; now I have two problems' game and rightly decided against supporting it.
Standard Syntax
SELECT REGEXP_REPLACE(string, pattern, replacement);
Version Support
MySQL: Native since 8.0
PostgreSQL: Native since 9.1
SQL Server: Not supported
Oracle: Native since 12c
SQLite: Not supported
Per-Database Syntax & Notes
MySQL Native syntax
REGEXP_REPLACE() added in MySQL 8.0.
SELECT REGEXP_REPLACE(string, pattern, replacement);
PostgreSQL Native syntax
SELECT REGEXP_REPLACE(string, pattern, replacement);
Oracle Native syntax
SELECT REGEXP_REPLACE(string, pattern, replacement) FROM dual;