regexp_replace

On this page

Replaces all regular expression matches in a string.

Syntax

regexp_replace(<string>, regex => <regex>, replacement => <replacement>)

Arguments

ArgumentTypeDescription
<string>stringString to transform.
regexstringConstant. Regular expression. Must be valid and non-NULL.
replacementstringConstant. Replacement text. Use $1 or ${name} to refer to capture groups. Must not be NULL.

Returns

Returns string. If there is no match, returns the input unchanged. Returns NULL if the input is NULL.

Examples

Replace numeric order IDs with a route placeholder so individual orders do not become separate groups.

VALUES ('/orders/17'), ('/orders/42'), ('/orders/42/refund')
SELECT $0 AS path
SELECT regexp_replace(path, regex => '/[0-9]+', replacement => '/:id') AS route
GROUP BY route
AGGREGATE count() AS requests
ORDER BY route
+--------------------+----------+
| route              | requests |
+--------------------+----------+
| /orders/:id        | 2        |
| /orders/:id/refund | 1        |
+--------------------+----------+