String replacement functions ​

Replacement functions change text using literal matches, character translation, positional overlays or regular expressions. Choose the operation that matches the intended replacement rule.

replace matches a literal substring, translate maps individual characters, and overlay uses character positions. regexp_replace interprets its pattern and replacement syntax as documented below.

Example: Replacing separators ​

SELECT replace('2025/03/05', '/', '-'),
       translate('a-b-c', '-', '_')
FORMAT csv;

"2025-03-05","a_b_c"

overlay ​

Replace a character-position substring.

Syntax ​

overlay(text,replacement,start[,length])

Arguments ​

One-based positive start; nonnegative length defaults to replacement character count; Unicode character positions. Comma argument form.

Returned value ​

TEXT; starts beyond the source append replacement; SQL NULL propagates.

Example ​

SELECT overlay('Txxxxas','hom',2,4) FORMAT csv;

"Thomas"

regexp_replace ​

Replace a selected match or all regex matches using PostgreSQL replacement references.

Syntax ​

regexp_replace(value, pattern, replacement[, flags]), regexp_replace(value, pattern, replacement, start[, occurrence[, flags]])

Arguments ​

Flags support i,c,s,n,m,p,w,x,t,q,e and g; active q and x modes are incompatible. Start is one-based; occurrence 0 replaces all. Replacement backslash-digit references groups and backslash-ampersand references the entire match. Unsupported regex constructs error.

Returned value ​

Replaced TEXT, or NULL for any NULL argument. Regular-expression operations remain subject to request limits.

Example ​

SELECT regexp_replace('a1b2','[0-9]','X','g') FORMAT csv;

"aXbX"

replace ​

Replace all non-overlapping, case-sensitive occurrences of a search value.

Syntax ​

replace(value, search, replacement)

Arguments ​

Three SQL values converted to byte sequences. A NULL value or search returns NULL. Empty search returns the text-converted source before checking replacement; otherwise NULL replacement returns NULL.

Returned value ​

TEXT, including for an empty search. NULL arguments propagate as described; oversized output raises an error.

Example ​

SELECT replace('a-b-a','a','x') FORMAT csv;

"x-b-x"

translate ​

Map characters by their positions in source and replacement sets; missing replacements delete characters.

Syntax ​

translate(text, from, to)

Arguments ​

Unicode character sets; the first occurrence of each repeated source character defines its mapping.

Returned value ​

TEXT, or NULL for any NULL argument.

Example ​

SELECT translate('12345','143','ax') FORMAT csv;

"a2x5"