# String functions

String functions inspect, slice, normalize, compare and transform text. Character-based positions count Unicode characters; byte-oriented functions such as `octet_length` and encoding functions count the underlying bytes.

`lower` and `upper` have legacy ASCII behavior. Use `lower_utf8`, `upper_utf8`, `casefold` or other explicitly documented Unicode operations when their broader behavior is required.

## Example: Normalizing a text field

```sql
SELECT lower(trim('  ADA@EXAMPLE.ORG  ')) AS normalized_email
FORMAT csv;
```

```text
"ada@example.org"
```

## ascii

Return the Unicode code point of the first character; return zero for empty text.

### Syntax

```text
ascii(text)
```

### Arguments

One text-convertible SQL value.

### Returned value

INTEGER code point, or NULL for NULL input.

### Example

```sql
SELECT ascii('A') FORMAT csv;
```

```text
65
```

## bit_length

Return UTF-8 text or exact BLOB length in bits.

### Syntax

```text
bit_length(value)
```

### Arguments

Text conversions use native scalar rules; BLOB counts raw bytes without decoding. Embedded NUL bytes are included.

### Returned value

INTEGER bit count or SQL NULL.

### Example

```sql
SELECT bit_length('é') FORMAT csv;
```

```text
16
```

## btrim

Remove supplied Unicode characters from both ends of text.

### Syntax

```text
btrim(value[,characters])
```

### Arguments

Default character set is one ASCII space. Empty set retains the input. Membership uses a fixed ASCII table and distinct Unicode characters. NULL propagates.

### Returned value

Trimmed TEXT or SQL NULL.

### Example

```sql
SELECT btrim('xyhelloyx','xy') FORMAT csv;
```

```text
"hello"
```

## casefold

Apply full default Unicode case folding for caseless comparison.

### Syntax

```text
casefold(text)
```

### Arguments

Locale-independent default folding; does not normalize Unicode; NULL propagates.

### Returned value

TEXT with full folded mappings, which may expand characters.

### Example

```sql
SELECT casefold('Straße') FORMAT csv;
```

```text
"strasse"
```

## chr

Convert a nonzero Unicode scalar code point to text.

### Syntax

```text
chr(code)
```

### Arguments

Signed integer; zero, surrogate values, negative values, and values above U+10FFFF error.

### Returned value

One Unicode character as TEXT, or NULL for NULL input.

### Example

```sql
SELECT chr(65) FORMAT csv;
```

```text
"A"
```

## convert

Convert bytes from one supported character encoding to another.

### Syntax

```text
convert(bytes,source_encoding,destination_encoding)
```

### Arguments

BLOB or UTF-8 text. Reuses UTF8/SQL_ASCII, LATIN1, WIN1250..1258, KOI8R/U mappings. Native SQL_ASCII follows strict UTF8. Undefined bytes, NUL, unsupported encodings and unrepresentable characters error. SQL NULL propagates.

### Returned value

BLOB destination bytes.

### Example

```sql
SELECT encode(convert(decode('e9','hex'),'LATIN1','UTF8'),'hex') FORMAT csv;
```

```text
"c3a9"
```

## initcap

Uppercase the first character of each alphanumeric word and lowercase remaining characters using locale-independent Unicode mappings.

### Syntax

```text
initcap(text)
```

### Arguments

One SQL value converted to text; words are Unicode alphanumeric sequences.

### Returned value

TEXT, or NULL for NULL input.

### Example

```sql
SELECT initcap('hi THOMAS') FORMAT csv;
```

```text
"Hi Thomas"
```

## left

Take text characters from the start, or omit trailing characters for a negative count.

### Syntax

```text
left(value, count)
```

### Arguments

Values use ordinary text conversion; count is an integer. Positions count Unicode characters, including embedded NUL.

### Returned value

Prefix TEXT, or NULL for NULL input.

### Example

```sql
SELECT left('abcdef',3) FORMAT csv;
```

```text
"abc"
```

## length

Measure text characters, binary bytes, or native collection elements.

### Syntax

```text
length(value)
```

### Arguments

value: text counts Unicode characters before the first NUL; BLOB and FixedString count bytes; collections count elements; QBit counts dimensions. Other values use their text representation.

### Returned value

Nonnegative INTEGER length, or NULL for a NULL argument.

### Example

```sql
SELECT length('hello'),length('é'),length(x'00FF') FORMAT csv;
```

```text
5,1,2
```

## levenshtein

Compute Unicode edit distance.

### Syntax

```text
levenshtein(source,target); optional insertion/deletion/substitution costs
```

### Arguments

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. SQL NULL propagates.

### Returned value

INTEGER edit distance.

### Example

```sql
SELECT levenshtein('GUMBO','GAMBOL') FORMAT csv;
```

```text
2
```

## levenshtein_less_equal

Compute Unicode edit distance up to a threshold.

### Syntax

```text
levenshtein_less_equal(source,target,max_distance); optional insertion/deletion/substitution costs
```

### Arguments

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. Threshold is last; negative threshold requests the full distance. Above threshold any larger value is valid. SQL NULL propagates.

### Returned value

INTEGER edit distance or a value greater than the threshold.

### Example

```sql
SELECT levenshtein_less_equal('GUMBO','GAMBOL',2) FORMAT csv;
```

```text
2
```

## lower

Convert a value to text and lowercase ASCII letters; preserve other characters and NULL.

### Syntax

```text
lower(value)
```

### Arguments

value: one SQL value, converted to text unless NULL.

### Returned value

TEXT with ASCII letters lowercased, or NULL for a NULL argument.

### Example

```sql
SELECT lower('HELLO') FORMAT csv;
```

```text
"hello"
```

## lower_utf8

Lowercase text with locale-independent Unicode case mapping, including Cyrillic.

### Syntax

```text
lower_utf8(value)
```

### Arguments

value: one SQL value converted to text. Invalid blob UTF-8 is replaced during text conversion; NULL propagates.

### Returned value

Lowercase TEXT; Unicode mappings may change its byte length or character count. NULL for a NULL argument.

### Example

```sql
SELECT lower_utf8('HELLO ÄBC') FORMAT csv;
```

```text
"hello äbc"
```

## lpad

Pad text on the left or truncate it on the right to a character length.

### Syntax

```text
lpad(value, length[, fill])
```

### Arguments

Fill defaults to one space; fill is repeated and its final repetition may be partial. Negative length returns empty text; empty fill adds nothing.

### Returned value

Padded or truncated TEXT; NULL propagates; excessive result bytes raise STRING_TOO_LONG.

### Example

```sql
SELECT lpad('hi',5,'xy') FORMAT csv;
```

```text
"xyxhi"
```

## md5

Compute the lowercase hexadecimal MD5 digest of text bytes.

### Syntax

```text
md5(text)
```

### Arguments

Text conversion uses UTF-8 bytes; NULL propagates.

### Returned value

32-character lowercase hexadecimal TEXT.

### Example

```sql
SELECT md5('abc') FORMAT csv;
```

```text
"900150983cd24fb0d6963f7d28e17f72"
```

## normalize

Normalize Unicode text using NFC by default or an explicitly selected normalization form.

### Syntax

```text
normalize(text[, NFC|NFD|NFKC|NFKD])
```

### Arguments

Forms may also be text/parameters; Unicode normalization is locale-independent; NULL propagates.

### Returned value

TEXT in the selected normalization form.

### Example

```sql
SELECT normalize('①',NFKC) FORMAT csv;
```

```text
"1"
```

## 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"
```

## parse_ident

Parse a dotted SQL identifier into an array of components without truncation.

### Syntax

```text
parse_ident(text[,strict])
```

### Arguments

Strict defaults to true; false ignores trailing text after the final identifier. Quoted names retain spelling and doubled double quotes; unquoted names fold ASCII case. Empty/malformed components and NUL error.

### Returned value

Native Array(String), or NULL for a NULL argument.

### Example

```sql
SELECT array_to_string(parse_ident('"SomeSchema".SomeTable'),'/') FORMAT csv;
```

```text
"SomeSchema/sometable"
```

## quote_ident

Quote an identifier for the native SQL grammar.

### Syntax

```text
quote_ident(value)
```

### Arguments

Conservative quoting always uses double quotes, doubling embedded quotes; NUL errors.

### Returned value

TEXT containing a quoted SQL identifier; SQL NULL propagates.

### Example

```sql
SELECT quote_ident('Foo bar') FORMAT csv;
```

```text
"""Foo bar"""
```

## quote_literal

Quote a literal for the native SQL string grammar.

### Syntax

```text
quote_literal(value)
```

### Arguments

Scalar text is single-quoted and embedded quotes doubled. Native backslashes remain literal; no PostgreSQL E-string prefix. NUL errors.

### Returned value

TEXT containing a SQL literal; SQL NULL propagates.

### Example

```sql
SELECT quote_literal('O''Reilly') FORMAT csv;
```

```text
"'O''Reilly'"
```

## quote_nullable

Quote a literal or produce the SQL NULL keyword.

### Syntax

```text
quote_nullable(value)
```

### Arguments

Same native quoted-string contract as quote_literal.

### Returned value

TEXT; a SQL NULL input returns the text NULL.

### Example

```sql
SELECT quote_nullable(NULL) FORMAT csv;
```

```text
"NULL"
```

## repeat

Repeat text a requested number of times, subject to result-byte limits.

### Syntax

```text
repeat(text, count)
```

### Arguments

Text and signed integer count; nonpositive count gives empty text; results above the string byte ceiling error.

### Returned value

TEXT, or NULL for any NULL argument.

### Example

```sql
SELECT repeat('Pg',3) FORMAT csv;
```

```text
"PgPgPg"
```

## 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"
```

## reverse

Reverse Unicode scalar characters, without reversing the bytes within a character.

### Syntax

```text
reverse(text)
```

### Arguments

One SQL value converted to text; combining sequences are not treated as one grapheme.

### Returned value

TEXT, or NULL for NULL input.

### Example

```sql
SELECT reverse('abc') FORMAT csv;
```

```text
"cba"
```

## right

Take text characters from the end, or omit leading characters for a negative count.

### Syntax

```text
right(value, count)
```

### Arguments

Values use ordinary text conversion; count is an integer. Positions count Unicode characters, including embedded NUL.

### Returned value

Suffix TEXT, or NULL for NULL input.

### Example

```sql
SELECT right('abcdef',3) FORMAT csv;
```

```text
"def"
```

## rpad

Pad text on the right or truncate it on the right to a character length.

### Syntax

```text
rpad(value, length[, fill])
```

### Arguments

Fill defaults to one space; fill is repeated and its final repetition may be partial. Negative length returns empty text; empty fill adds nothing.

### Returned value

Padded or truncated TEXT; NULL propagates; excessive result bytes raise STRING_TOO_LONG.

### Example

```sql
SELECT rpad('hi',5,'xy') FORMAT csv;
```

```text
"hixyx"
```

## show_trgm

List sorted unique compact trigrams.

### Syntax

```text
show_trgm(text)
```

### Arguments

Same Unicode word/padding/lowercase rules as similarity. Multibyte compact keys use PostgreSQL legacy CRC and little-endian byte extraction. NUL errors. SQL NULL propagates.

### Returned value

Array(String): printable ASCII keys or hexadecimal compact keys.

### Example

```sql
SELECT array_to_string(show_trgm('cat'),'|') FORMAT csv;
```

```text
"  c| ca|at |cat"
```

## similarity

Compare unique trigram sets.

### Syntax

```text
similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT similarity('word','two words') FORMAT csv;
```

```text
0.36363637
```

## split_part

Return one delimiter-separated field from text.

### Syntax

```text
split_part(value, delimiter, field)
```

### Arguments

Positive fields count from one; negative fields count from the last field; zero raises INVALID_ARGUMENT. Empty delimiter leaves one field.

### Returned value

Field TEXT, empty TEXT when missing, or NULL for any NULL argument.

### Example

```sql
SELECT split_part('a::b::c','::',2) FORMAT csv;
```

```text
"b"
```

## starts_with

Check whether text begins with a case-sensitive prefix.

### Syntax

```text
starts_with(text, prefix)
```

### Arguments

Two text-convertible values; empty prefix matches every string.

### Returned value

Native Bool, or NULL for a NULL argument.

### Example

```sql
SELECT starts_with('alphabet','alph') FORMAT csv;
```

```text
true
```

## strict_word_similarity

Maximize similarity over extents aligned to whole-word boundaries.

### Syntax

```text
strict_word_similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT strict_word_similarity('word','two words') FORMAT csv;
```

```text
0.5714286
```

## strpos

Find the first exact substring and count its one-based position in Unicode characters.

### Syntax

```text
strpos(text, substring)
```

### Arguments

Text and exact substring; empty substring starts at position one.

### Returned value

INTEGER position, 0 for no match, or NULL for a NULL argument.

### Example

```sql
SELECT strpos('high','ig') FORMAT csv;
```

```text
2
```

## to_ascii

Map native Unicode text to ASCII through a PostgreSQL single-byte repertoire.

### Syntax

```text
to_ascii(text[,encoding])
```

### Arguments

Native default LATIN1. Supports LATIN1/LATIN2/LATIN9/WIN1250 and PostgreSQL numeric IDs 8/9/16/29. Encode Unicode characters in that repertoire before applying its ASCII map; unrepresentable characters and NUL error. This differs from PostgreSQL applying a mapping directly to database-encoded bytes.

### Returned value

ASCII TEXT retaining one output character per input character, or NULL for a NULL argument.

### Example

```sql
SELECT to_ascii('Karél') FORMAT csv;
```

```text
"Karel"
```

## to_bin

Format an integer in binary.

### Syntax

```text
to_bin(integer)
```

### Arguments

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

### Returned value

TEXT two's-complement binary representation.

### Example

```sql
SELECT to_bin(10) FORMAT csv;
```

```text
"1010"
```

## to_hex

Render an exact signed Int64 or unsigned UInt64 in hexadecimal.

### Syntax

```text
to_hex(integer)
```

### Arguments

Negative signed arguments use 64-bit two's complement; oversized signed fields error; NULL propagates.

### Returned value

Lowercase hexadecimal TEXT without a prefix.

### Example

```sql
SELECT to_hex(2147483647) FORMAT csv;
```

```text
"7fffffff"
```

## to_oct

Format an integer in octal.

### Syntax

```text
to_oct(integer)
```

### Arguments

Native exact integers through 256 bits. Negative values use the declared integer width, or runtime width when no declaration is bound. Ordinary INTEGER inputs use 64 bits. SQL NULL propagates.

### Returned value

TEXT two's-complement octal representation.

### Example

```sql
SELECT to_oct(10) FORMAT csv;
```

```text
"12"
```

## 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"
```

## trim

Remove selected characters from both ends of a text value; default to the space character.

### Syntax

```text
trim(value[, characters])
```

### Arguments

value: converted to text. Optional characters: a set of individual characters, not a substring. NULL in either supplied argument returns NULL.

### Returned value

Trimmed TEXT. An empty character set retains the text; a NULL argument returns NULL.

### Example

```sql
SELECT trim('  hello  ') FORMAT csv;
```

```text
"hello"
```

## unaccent

Apply PostgreSQL 18 default unaccent mappings.

### Syntax

```text
unaccent([dictionary,]text)
```

### Arguments

Native UTF-8 text; default mappings include ligature expansion and combining-mark removal. Named dictionary must be unaccent/public.unaccent/pg_catalog.unaccent. NUL and unknown dictionary error. SQL NULL propagates.

### Returned value

TEXT with mapped accents removed.

### Example

```sql
SELECT unaccent('Hôtel Æther') FORMAT csv;
```

```text
"Hotel AEther"
```

## unicode_assigned

Check all scalar characters against Unicode assignment data.

### Syntax

```text
unicode_assigned(text)
```

### Arguments

UTF-8 native text. Uses Unicode 17.0 data from the existing normalization library and recognizes private-use code points as assigned. SQL NULL propagates.

### Returned value

BOOL; true for empty text.

### Example

```sql
SELECT unicode_assigned('Hello') FORMAT csv;
```

```text
true
```

## unistr

Decode escaped Unicode code points and adjacent UTF-16 surrogate pairs.

### Syntax

```text
unistr(text)
```

### Arguments

Accepts backslash plus four hex digits, u plus four, U plus eight, or + plus six; doubled backslashes are literal. Invalid escapes, unpaired surrogates, NUL, and out-of-range points error.

### Returned value

Native UTF-8 TEXT, or NULL for SQL NULL.

### Example

```sql
SELECT unistr('d\0061t\+000061') FORMAT csv;
```

```text
"data"
```

## upper

Convert a value to text and uppercase ASCII letters; preserve other characters and NULL.

### Syntax

```text
upper(value)
```

### Arguments

value: one SQL value, converted to text unless NULL.

### Returned value

TEXT with ASCII letters uppercased, or NULL for a NULL argument.

### Example

```sql
SELECT upper('hello') FORMAT csv;
```

```text
"HELLO"
```

## upper_utf8

Uppercase text with locale-independent Unicode case mapping, including Cyrillic.

### Syntax

```text
upper_utf8(value)
```

### Arguments

value: one SQL value converted to text. Invalid blob UTF-8 is replaced during text conversion; NULL propagates.

### Returned value

Uppercase TEXT; Unicode mappings may expand characters, such as sharp S to SS. NULL for a NULL argument.

### Example

```sql
SELECT upper_utf8('hello Straße') FORMAT csv;
```

```text
"HELLO STRASSE"
```

## word_similarity

Maximize similarity to a contiguous extent of text trigrams.

### Syntax

```text
word_similarity(pattern,text)
```

### Arguments

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

### Returned value

Native Float32 score in \[0,1]; zero for empty trigram sets.

### Example

```sql
SELECT word_similarity('word','two words') FORMAT csv;
```

```text
0.8
```

## Core string functions

`substr(text,start[,length])` and substring use character positions starting at 1 and allow negative starts. Text slicing stops at the first NUL; BLOB slicing retains all bytes. `instr(text,part)` returns a one-based position or zero. `ltrim` and `rtrim` strip characters from one end. concat joins values; concat_ws requires a separator and at least one value. printf/format accept a format string and values; quote produces an SQL literal. lower/upper use ASCII mapping; lower_utf8/upper_utf8 use Unicode mapping.

```sql
SELECT substr('hello',2,3),instr('hello','ll'),concat('a','b'),concat_ws('-','a','b'),printf('%04d',7) FORMAT csv;
```

```text
"ell",3,"ab","a-b","0007"
```

## octet_length

Returns the full byte length of text or a BLOB, including bytes after an embedded NUL. Text uses UTF-8; unlike length, it counts bytes rather than Unicode characters. NULL returns NULL.

```text
octet_length(value)
```

```sql
SELECT length('é'),octet_length('é'),octet_length('a'||char(0)||'b') FORMAT csv;
```

```text
1,2,3
```