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 ​

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

"ada@example.org"

ascii ​

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

Syntax ​

ascii(text)

Arguments ​

One text-convertible SQL value.

Returned value ​

INTEGER code point, or NULL for NULL input.

Example ​

SELECT ascii('A') FORMAT csv;

65

bit_length ​

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

Syntax ​

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 ​

SELECT bit_length('é') FORMAT csv;

16

btrim ​

Remove supplied Unicode characters from both ends of text.

Syntax ​

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 ​

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

"hello"

casefold ​

Apply full default Unicode case folding for caseless comparison.

Syntax ​

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 ​

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

"strasse"

chr ​

Convert a nonzero Unicode scalar code point to text.

Syntax ​

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 ​

SELECT chr(65) FORMAT csv;

"A"

convert ​

Convert bytes from one supported character encoding to another.

Syntax ​

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 ​

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

"c3a9"

initcap ​

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

Syntax ​

initcap(text)

Arguments ​

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

Returned value ​

TEXT, or NULL for NULL input.

Example ​

SELECT initcap('hi THOMAS') FORMAT csv;

"Hi Thomas"

left ​

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

Syntax ​

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 ​

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

"abc"

length ​

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

Syntax ​

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 ​

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

5,1,2

levenshtein ​

Compute Unicode edit distance.

Syntax ​

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 ​

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

2

levenshtein_less_equal ​

Compute Unicode edit distance up to a threshold.

Syntax ​

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 ​

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

2

lower ​

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

Syntax ​

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 ​

SELECT lower('HELLO') FORMAT csv;

"hello"

lower_utf8 ​

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

Syntax ​

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 ​

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

"hello äbc"

lpad ​

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

Syntax ​

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 ​

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

"xyxhi"

md5 ​

Compute the lowercase hexadecimal MD5 digest of text bytes.

Syntax ​

md5(text)

Arguments ​

Text conversion uses UTF-8 bytes; NULL propagates.

Returned value ​

32-character lowercase hexadecimal TEXT.

Example ​

SELECT md5('abc') FORMAT csv;

"900150983cd24fb0d6963f7d28e17f72"

normalize ​

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

Syntax ​

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 ​

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

"1"

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"

parse_ident ​

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

Syntax ​

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 ​

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

"SomeSchema/sometable"

quote_ident ​

Quote an identifier for the native SQL grammar.

Syntax ​

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 ​

SELECT quote_ident('Foo bar') FORMAT csv;

"""Foo bar"""

quote_literal ​

Quote a literal for the native SQL string grammar.

Syntax ​

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 ​

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

"'O''Reilly'"

quote_nullable ​

Quote a literal or produce the SQL NULL keyword.

Syntax ​

quote_nullable(value)

Arguments ​

Same native quoted-string contract as quote_literal.

Returned value ​

TEXT; a SQL NULL input returns the text NULL.

Example ​

SELECT quote_nullable(NULL) FORMAT csv;

"NULL"

repeat ​

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

Syntax ​

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 ​

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

"PgPgPg"

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"

reverse ​

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

Syntax ​

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 ​

SELECT reverse('abc') FORMAT csv;

"cba"

right ​

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

Syntax ​

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 ​

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

"def"

rpad ​

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

Syntax ​

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 ​

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

"hixyx"

show_trgm ​

List sorted unique compact trigrams.

Syntax ​

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 ​

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

"  c| ca|at |cat"

similarity ​

Compare unique trigram sets.

Syntax ​

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 ​

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

0.36363637

split_part ​

Return one delimiter-separated field from text.

Syntax ​

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 ​

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

"b"

starts_with ​

Check whether text begins with a case-sensitive prefix.

Syntax ​

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 ​

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

true

strict_word_similarity ​

Maximize similarity over extents aligned to whole-word boundaries.

Syntax ​

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 ​

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

0.5714286

strpos ​

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

Syntax ​

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 ​

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

2

to_ascii ​

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

Syntax ​

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 ​

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

"Karel"

to_bin ​

Format an integer in binary.

Syntax ​

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 ​

SELECT to_bin(10) FORMAT csv;

"1010"

to_hex ​

Render an exact signed Int64 or unsigned UInt64 in hexadecimal.

Syntax ​

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 ​

SELECT to_hex(2147483647) FORMAT csv;

"7fffffff"

to_oct ​

Format an integer in octal.

Syntax ​

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 ​

SELECT to_oct(10) FORMAT csv;

"12"

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"

trim ​

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

Syntax ​

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 ​

SELECT trim('  hello  ') FORMAT csv;

"hello"

unaccent ​

Apply PostgreSQL 18 default unaccent mappings.

Syntax ​

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 ​

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

"Hotel AEther"

unicode_assigned ​

Check all scalar characters against Unicode assignment data.

Syntax ​

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 ​

SELECT unicode_assigned('Hello') FORMAT csv;

true

unistr ​

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

Syntax ​

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 ​

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

"data"

upper ​

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

Syntax ​

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 ​

SELECT upper('hello') FORMAT csv;

"HELLO"

upper_utf8 ​

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

Syntax ​

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 ​

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

"HELLO STRASSE"

word_similarity ​

Maximize similarity to a contiguous extent of text trigrams.

Syntax ​

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 ​

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

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.

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

"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.

octet_length(value)

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

1,2,3