# 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

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

```text
"2025-03-05","a_b_c"
```

## overlay

Replace a character-position substring.

### Syntax

```text
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

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

```text
"Thomas"
```

## regexp_replace

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

### Syntax

```text
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

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

```text
"aXbX"
```

## replace

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

### Syntax

```text
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

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

```text
"x-b-x"
```

## translate

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

### Syntax

```text
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

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

```text
"a2x5"
```