How to use string functions and operators-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports the string functions and operators of PostgreSQL 12. This page lists all supported functions with syntax, return types, and examples.

For the full PostgreSQL 12 reference, see String Functions and Operators.

SQL string operators and functions

These functions and operators are defined by the SQL standard.

Function	Return type	Description	Example	Result
`_string_ \|\| _string_`	`text`	Concatenates two strings.	`'Post' \|\| 'greSQL'`	`PostgreSQL`
`_string_ \|\| _non-string_` or `_non-string_ \|\| _string_`	`text`	Concatenates a string and a non-string value. The non-string operand is coerced to text.	`'Value: ' \|\| 42`	`Value: 42`
`bit_length(_string_)`	`int`	Returns the number of bits in a string.	`bit_length('jose')`	`32`
`char_length(_string_)` or `character_length(_string_)`	`int`	Returns the number of characters in a string. These two functions are synonymous.	`char_length('jose')`	`4`
`lower(_string_)`	`text`	Converts all characters to lowercase.	`lower('TOM')`	`tom`
`octet_length(_string_)`	`int`	Returns the byte length of a string. For multi-byte encodings, this differs from `char_length`.	`octet_length('jose')`	`4`
`overlay(_string_ placing _replacement_ from _start_ [for _count_])`	`text`	Replaces the substring of _string_ starting at position _start_ for _count_ characters with _replacement_. If `for _count_` is omitted, the replacement length equals the length of _replacement_.	`overlay('Txxxxas' placing 'hom' from 2 for 4)`	`Thomas`
`position(_substring_ in _string_)`	`int`	Returns the starting position of the first match of _substring_ in _string_. Returns `0` if _substring_ is not found.	`position('om' in 'Thomas')`	`3`
`substring(_string_ [from _int_] [for _int_])`	`text`	Extracts a substring by start position and length.	`substring('Thomas' from 2 for 3)`	`hom`
`substring(_string_ from _pattern_)`	`text`	Extracts the first substring matching a POSIX regular expression. Returns NULL if there is no match.	`substring('Thomas' from '...$')`	`mas`
`substring(_string_ from _pattern_ for _escape_)`	`text`	Extracts a substring matching an SQL regular expression.	`substring('Thomas' from '%#"o_a#"_' for '#')`	`oma`
`trim([leading \| trailing \| both] [_characters_] from _string_)`	`text`	Removes the longest run of the specified characters from the start, end, or both ends of _string_. Removes spaces if _characters_ is not specified. The default is `both`.	`trim(both 'xyz' from 'yxTomxx')`	`Tom`
`trim([leading \| trailing \| both] [from] _string_ [, _characters_])`	`text`	Alternative syntax for `trim`.	`trim(both from 'xTomxx', 'x')`	`Tom`
`upper(_string_)`	`text`	Converts all characters to uppercase.	`upper('tom')`	`TOM`

Other string functions

Case conversion

Function	Return type	Description	Example	Result
`initcap(_string_)`	`text`	Capitalizes the first letter of each word and lowercases the rest. Word boundaries are sequences of non-alphanumeric characters.	`initcap('hi THOMAS')`	`Hi Thomas`

For lower and upper, see SQL string operators and functions.

Concatenation

Function	Return type	Description	Example	Result
`concat(_val1_ [, _val2_ [, ...]])`	`text`	Concatenates all arguments as text. NULL arguments are silently ignored, unlike the `\|\|` operator which returns NULL if any operand is NULL.	`concat('abcde', 2, NULL, 22)`	`abcde222`
`concat_ws(_separator_, _val1_ [, _val2_ [, ...]])`	`text`	Concatenates arguments with _separator_ between each adjacent pair. NULL arguments are skipped; the separator is not added for NULL arguments.	`concat_ws(',', 'abcde', 2, NULL, 22)`	`abcde,2,22`
`repeat(_string_, _n_)`	`text`	Repeats _string_ exactly _n_ times. Returns an empty string if _n_ is zero or negative.	`repeat('Pg', 4)`	`PgPgPgPg`

Substring extraction

Function	Return type	Description	Example	Result
`left(_string_, _n_)`	`text`	Returns the first _n_ characters of _string_. If _n_ is negative, returns all characters except the last `\|_n_\|` characters.	`left('abcde', 2)`	`ab`
`right(_string_, _n_)`	`text`	Returns the last _n_ characters of _string_. If _n_ is negative, returns all characters except the first `\|_n_\|` characters.	`right('abcde', 2)`	`de`
`substr(_string_, _start_ [, _count_])`	`text`	Extracts a substring starting at position _start_ (1-indexed), optionally limited to _count_ characters. Equivalent to `substring(_string_ from _start_ for _count_)`.	`substr('alphabet', 3, 2)`	`ph`
`split_part(_string_, _delimiter_, _n_)`	`text`	Splits _string_ on _delimiter_ and returns the _n_-th part (1-indexed). Returns an empty string if _n_ is greater than the number of parts.	`split_part('abc~@~def~@~ghi', '~@~', 2)`	`def`

Search and position

Function	Return type	Description	Example	Result
`starts_with(_string_, _prefix_)`	`bool`	Returns `true` if _string_ starts with _prefix_; otherwise returns `false`.	`starts_with('alphabet', 'alph')`	`t`
`strpos(_string_, _substring_)`	`int`	Returns the position of the first occurrence of _substring_ in _string_. Returns `0` if not found. Equivalent to `position(_substring_ in _string_)`.	`strpos('high', 'ig')`	`2`

Padding and trimming

Function	Return type	Description	Example	Result
`btrim(_string_ [, _characters_])`	`text`	Removes the longest run of any character in _characters_ from both ends of _string_. Removes whitespace by default.	`btrim('xyxtrimyyx', 'xyz')`	`trim`
`lpad(_string_, _length_ [, _fill_])`	`text`	Pads _string_ on the left with _fill_ (default: space) to a total length of _length_ characters. If _string_ is already longer than _length_, it is truncated on the right.	`lpad('hi', 5, 'xy')`	`xyxhi`
`ltrim(_string_ [, _characters_])`	`text`	Removes the longest run of any character in _characters_ from the left end of _string_. Removes whitespace (spaces, tabs, and newlines) by default.	`ltrim('zzzytest', 'xyz')`	`test`
`rpad(_string_, _length_ [, _fill_])`	`text`	Pads _string_ on the right with _fill_ (default: space) to a total length of _length_ characters. If _string_ is already longer than _length_, it is truncated on the right.	`rpad('hi', 5, 'xy')`	`hixyx`
`rtrim(_string_ [, _characters_])`	`text`	Removes the longest run of any character in _characters_ from the right end of _string_. Removes whitespace by default.	`rtrim('testxxzx', 'xyz')`	`test`

String replacement

Function	Return type	Description	Example	Result
`replace(_string_, _from_, _to_)`	`text`	Replaces all occurrences of _from_ in _string_ with _to_. Use `regexp_replace` for pattern-based replacement; use `translate` for character-by-character substitution.	`replace('abcdefabcdef', 'cd', 'XX')`	`abXXefabXXef`
`translate(_string_, _from_, _to_)`	`text`	Replaces each character in _string_ that appears in _from_ with the corresponding character in _to_. Characters in _from_ with no counterpart in _to_ are deleted. Differs from `replace`, which replaces a full substring, and from `regexp_replace`, which uses pattern matching.	`translate('12345', '143', 'ax')`	`a2x5`
`reverse(_string_)`	`text`	Returns the characters of _string_ in reverse order.	`reverse('abcde')`	`edcba`

For overlay, see SQL string operators and functions.

Regular expression functions

All functions in this group use POSIX regular expression syntax unless noted otherwise.

Function	Return type	Description	Example	Result
`regexp_match(_string_, _pattern_ [, _flags_])`	`text[]`	Returns a text array of the first match of _pattern_ in _string_. Returns NULL if there is no match. To get all matches, use `regexp_matches` with the `g` flag.	`regexp_match('foobarbequebaz', '(bar)(beque)')`	`{bar,beque}`
`regexp_matches(_string_, _pattern_ [, _flags_])`	`setof text[]`	Returns a set of text arrays, one per match of _pattern_ in _string_.	`regexp_matches('foobarbequebaz', 'ba.', 'g')`	`{bar}` `{baz}` (2 rows)
`regexp_replace(_string_, _pattern_, _replacement_ [, _flags_])`	`text`	Replaces the first match of _pattern_ in _string_ with _replacement_. Use the `g` flag to replace all matches. Similar to `replace`, but uses POSIX pattern matching instead of literal string matching.	`regexp_replace('Thomas', '.[mN]a.', 'M')`	`ThM`
`regexp_split_to_array(_string_, _delimiter_ [, _flags_])`	`text[]`	Splits _string_ using a POSIX regular expression as the delimiter and returns the parts as an array.	`regexp_split_to_array('hello world', '\s+')`	`{hello,world}`
`regexp_split_to_table(_string_, _delimiter_ [, _flags_])`	`setof text`	Splits _string_ using a POSIX regular expression as the delimiter and returns one part per row.	`regexp_split_to_table('hello world', '\s+')`	`hello` `world` (2 rows)

Length and measurement

Function	Return type	Description	Example	Result
`length(_string_)`	`int`	Returns the number of characters in _string_, including spaces and special characters. Equivalent to `char_length`.	`length('jose')`	`4`
`length(_string_, _encoding_)`	`int`	Returns the number of characters in _string_ when interpreted in the given encoding.	`length('jose', 'UTF8')`	`4`

For bit_length, char_length, and octet_length, see SQL string operators and functions.

ASCII and character conversion

Function	Return type	Description	Example	Result
`ascii(_string_)`	`int`	Returns the numeric code of the first character of _string_. For ASCII characters, this is the ASCII code. For multi-byte encodings, returns the Unicode code point.	`ascii('x')`	`120`
`chr(_code_)`	`text`	Returns the character with the given ASCII or Unicode code point. `chr(0)` is not allowed.	`chr(65)`	`A`
`to_ascii(_string_ [, _encoding_])`	`text`	Converts _string_ to ASCII from the given encoding. Only `LATIN1`, `LATIN2`, `LATIN9`, and `WIN1250` source encodings are supported. Passing any other encoding raises an error.	`to_ascii('Karel')`	`Karel`
`to_hex(_number_)`	`text`	Converts an `integer` or `bigint` value to its lowercase hexadecimal string representation.	`to_hex(2147483647)`	`7fffffff`

Encoding conversion

Function	Return type	Description	Example	Result
`convert(_string_ bytea, _src_encoding_ name, _dest_encoding_ name)`	`bytea`	Converts binary string _string_ from _src_encoding_ to _dest_encoding_.	`convert('text_in_utf8', 'UTF8', 'LATIN1')`	`text_in_utf8` in Latin-1 (ISO 8859-1) encoding
`convert_from(_string_ bytea, _src_encoding_ name)`	`text`	Decodes the `bytea` binary string _string_ to `text` using _src_encoding_. The result is in the current database encoding.	`convert_from('text_in_utf8', 'UTF8')`	`text_in_utf8` in the current database encoding
`convert_to(_string_ text, _dest_encoding_ name)`	`bytea`	Encodes the `text` string _string_ to `bytea` using _dest_encoding_.	`convert_to('some text', 'UTF8')`	`some text` in UTF-8 encoding
`encode(_data_ bytea, _format_ text)`	`text`	Encodes binary data as a text string in the given format. Supported formats: `base64`, `hex`, and `escape`. The `escape` format represents non-printable bytes as backslash sequences.	`encode('123\000\001', 'base64')`	`MTIzAAE=`
`decode(_string_ text, _format_ text)`	`bytea`	Decodes a text string encoded in the given format back to binary. Supported formats: `base64`, `hex`, and `escape`.	`decode('MTIzAAE=', 'base64')`	`\x3132330001`
`pg_client_encoding()`	`name`	Returns the current client encoding name.	`pg_client_encoding()`	`SQL_ASCII`

Formatting and quoting

Function	Return type	Description	Example	Result
`format(_formatstr_ text [, _formatarg_ [, ...]])`	`text`	Formats a string using printf-style format specifiers. Supports `%s` (value as text), `%I` (SQL identifier), `%L` (SQL literal), and positional references such as `%1$s`.	`format('Hello %s, %1$s', 'World')`	`Hello World, World`
`md5(_string_)`	`text`	Returns the MD5 hash of _string_ as a 32-character lowercase hex string.	`md5('abc')`	`900150983cd24fb0d6963f7d28e17f72`
`quote_ident(_string_)`	`text`	Returns _string_ quoted as a SQL identifier, adding double quotes only if necessary (for example, if the identifier contains uppercase letters or special characters). Use this when building dynamic SQL to prevent SQL injection.	`quote_ident('Foo bar')`	`"Foo bar"`
`quote_literal(_string_)`	`text`	Returns _string_ as a properly quoted SQL string literal, with single quotes and internal single quotes escaped. Use `quote_nullable` if the value might be NULL.	`quote_literal(E'O\'Reilly')`	`'O''Reilly'`
`quote_literal(_value_ anyelement)`	`text`	Coerces _value_ to `text` and returns it as a quoted SQL string literal.	`quote_literal(42.5)`	`'42.5'`
`quote_nullable(_string_)`	`text`	Like `quote_literal`, but returns the unquoted string `NULL` if the argument is NULL.	`quote_nullable(NULL)`	`NULL`
`quote_nullable(_value_ anyelement)`	`text`	Like `quote_literal` for any type, but returns the unquoted string `NULL` if the argument is NULL.	`quote_nullable(42.5)`	`'42.5'`

SQL identifier parsing

Function	Return type	Description	Example	Result
`parse_ident(_qualified_identifier_ [, strict_mode boolean DEFAULT true])`	`text[]`	Parses a dot-separated SQL qualified identifier and returns its parts as an array, stripping any quoting. With `strict_mode` set to `false`, non-identifier characters after the last part are ignored.	`parse_ident('"SomeSchema".someTable')`	`{SomeSchema,sometable}`

Usage notes

NULL handling

concat and concat_ws silently skip NULL arguments. All other string functions return NULL if any argument is NULL, unless otherwise noted.
quote_nullable is designed for contexts where a value may be NULL: it returns the unquoted string NULL rather than a quoted empty string.

Position indexing

String positions are 1-indexed. The first character is at position 1.
position, strpos, and substr return 0 or an empty string, respectively, when no match or start position is found.
If _start_ is 0 in substr, it is treated as 1.

Negative indexes in `left` and `right`

left('abcde', -2) returns abc (all but the last 2 characters).
right('abcde', -2) returns cde (all but the first 2 characters).

`replace` vs `translate` vs `regexp_replace`

Choose based on what you are replacing:

Function	Replaces	Pattern support	Example use case
`replace`	A fixed substring	No	Replace all `http://` with `https://`
`translate`	Individual characters, one-for-one	No	Remove or swap specific characters
`regexp_replace`	A substring matching a POSIX pattern	Yes	Normalize whitespace, redact sensitive data

Encoding constraints for `to_ascii`

to_ascii supports only LATIN1, LATIN2, LATIN9, and WIN1250 as source encodings. Passing any other encoding raises an error.

`encode` and `decode` formats

Both functions support three formats: base64, hex, and escape. The escape format represents non-printable bytes as backslash sequences and non-ASCII bytes as octal escape sequences.