9.4. String Functions and Operators (original) (raw)
text ^@ text → boolean
Returns true if the first string starts with the second string (equivalent to the starts_with() function).
'alphabet' ^@ 'alph' → t
ascii ( text ) → integer
Returns the numeric code of the first character of the argument. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character.
ascii('x') → 120
chr ( integer ) → text
Returns the character with the given code. In UTF8 encoding the argument is treated as a Unicode code point. In other multibyte encodings the argument must designate an ASCII character. chr(0) is disallowed because text data types cannot store that character.
chr(65) → A
concat ( val1 "any" [, val2 "any" [, ...] ] ) → text
Concatenates the text representations of all the arguments. NULL arguments are ignored.
concat('abcde', 2, NULL, 22) → abcde222
concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.
concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
format ( formatstr text [, formatarg "any" [, ...] ] ) → text
Formats arguments according to a format string; see Section 9.4.1. This function is similar to the C function sprintf.
format('Hello %s, %1$s', 'World') → Hello World, World
initcap ( text ) → text
Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
initcap('hi THOMAS') → Hi Thomas
left ( string text, n integer ) → text
Returns first n characters in the string, or when n is negative, returns all but last |n| characters.
left('abcde', 2) → ab
length ( text ) → integer
Returns the number of characters in the string.
length('jose') → 4
md5 ( text ) → text
Computes the MD5 hash of the argument, with the result written in hexadecimal.
md5('abc') → 900150983cd24fb0d6963f7d28e17f72
parse_ident ( qualifiedidentifier text [, strictmode boolean DEFAULT true ] ) → text[]
Splits qualifiedidentifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions.) Note that this function does not truncate over-length identifiers. If you want truncation you can cast the result to name[].
parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}
pg_client_encoding ( ) → name
Returns current client encoding name.
pg_client_encoding() → UTF8
quote_ident ( text ) → text
Returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 41.1.
quote_ident('Foo bar') → "Foo bar"
quote_literal ( text ) → text
Returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 41.1.
quote_literal(E'O\'Reilly') → 'O''Reilly'
quote_literal ( anyelement ) → text
Converts the given value to text and then quotes it as a literal. Embedded single-quotes and backslashes are properly doubled.
quote_literal(42.5) → '42.5'
quote_nullable ( text ) → text
Returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 41.1.
quote_nullable(NULL) → NULL
quote_nullable ( anyelement ) → text
Converts the given value to text and then quotes it as a literal; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled.
quote_nullable(42.5) → '42.5'
regexp_count ( string text, pattern text [, start integer [, flags text ] ] ) → integer
Returns the number of times the POSIX regular expression pattern matches in the string; see Section 9.7.3.
regexp_count('123456789012', '\d\d\d', 2) → 3
regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer
Returns the position within string where the N'th match of the POSIX regular expression pattern occurs, or zero if there is no such match; see Section 9.7.3.
regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i') → 3
regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2) → 5
regexp_like ( string text, pattern text [, flags text ] ) → boolean
Checks whether a match of the POSIX regular expression pattern occurs within string; see Section 9.7.3.
regexp_like('Hello World', 'world$', 'i') → t
regexp_match ( string text, pattern text [, flags text ] ) → text[]
Returns substrings within the first match of the POSIX regular expression pattern to the string; see Section 9.7.3.
regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}
regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]
Returns substrings within the first match of the POSIX regular expression pattern to the string, or substrings within all such matches if the g flag is used; see Section 9.7.3.
regexp_matches('foobarbequebaz', 'ba.', 'g') →
{bar} {baz}
regexp_replace ( string text, pattern text, replacement text [, start integer ] [, flags text ] ) → text
Replaces the substring that is the first match to the POSIX regular expression pattern, or all such matches if the g flag is used; see Section 9.7.3.
regexp_replace('Thomas', '.[mN]a.', 'M') → ThM
regexp_replace ( string text, pattern text, replacement text, start integer, N integer [, flags text ] ) → text
Replaces the substring that is the N'th match to the POSIX regular expression pattern, or all such matches if N is zero; see Section 9.7.3.
regexp_replace('Thomas', '.', 'X', 3, 2) → ThoXas
regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]
Splits string using a POSIX regular expression as the delimiter, producing an array of results; see Section 9.7.3.
regexp_split_to_array('hello world', '\s+') → {hello,world}
regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text
Splits string using a POSIX regular expression as the delimiter, producing a set of results; see Section 9.7.3.
regexp_split_to_table('hello world', '\s+') →
hello world
regexp_substr ( string text, pattern text [, start integer [, N integer [, flags text [, subexpr integer ] ] ] ] ) → text
Returns the substring within string that matches the N'th occurrence of the POSIX regular expression pattern, or NULL if there is no such match; see Section 9.7.3.
regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i') → CDEF
regexp_substr('ABCDEF', 'c(.)(..)', 1, 1, 'i', 2) → EF
repeat ( string text, number integer ) → text
Repeats string the specified number of times.
repeat('Pg', 4) → PgPgPgPg
replace ( string text, from text, to text ) → text
Replaces all occurrences in string of substring from with substring to.
replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef
reverse ( text ) → text
Reverses the order of the characters in the string.
reverse('abcde') → edcba
right ( string text, n integer ) → text
Returns last n characters in the string, or when n is negative, returns all but first |n| characters.
right('abcde', 2) → de
split_part ( string text, delimiter text, n integer ) → text
Splits string at occurrences of delimiter and returns the n'th field (counting from one), or when n is negative, returns the |n|'th-from-last field.
split_part('abc~@~def~@~ghi', '~@~', 2) → def
split_part('abc,def,ghi,jkl', ',', -2) → ghi
starts_with ( string text, prefix text ) → boolean
Returns true if string starts with prefix.
starts_with('alphabet', 'alph') → t
string_to_array ( string text, delimiter text [, nullstring text ] ) → text[]
Splits the string at occurrences of delimiter and forms the resulting fields into a text array. If delimiter is NULL, each character in the string will become a separate element in the array. If delimiter is an empty string, then the string is treated as a single field. If nullstring is supplied and is not NULL, fields matching that string are replaced by NULL. See also array_to_string.
string_to_array('xx~~yy~~zz', '~~', 'yy') → {xx,NULL,zz}
string_to_table ( string text, delimiter text [, nullstring text ] ) → setof text
Splits the string at occurrences of delimiter and returns the resulting fields as a set of text rows. If delimiter is NULL, each character in the string will become a separate row of the result. If delimiter is an empty string, then the string is treated as a single field. If nullstring is supplied and is not NULL, fields matching that string are replaced by NULL.
string_to_table('xx~^~yy~^~zz', '~^~', 'yy') →
xx NULL zz
strpos ( string text, substring text ) → integer
Returns first starting index of the specified substring within string, or zero if it's not present. (Same as position(_`substring`_ in _`string`_), but note the reversed argument order.)
strpos('high', 'ig') → 2
substr ( string text, start integer [, count integer ] ) → text
Extracts the substring of string starting at the start'th character, and extending for count characters if that is specified. (Same as substring(_`string`_ from _`start`_ for _`count`_).)
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph
to_ascii ( string text ) → text
to_ascii ( string text, encoding name ) → text
to_ascii ( string text, encoding integer ) → text
Converts string to ASCII from another encoding, which may be identified by name or number. If encoding is omitted the database encoding is assumed (which in practice is the only useful case). The conversion consists primarily of dropping accents. Conversion is only supported from LATIN1, LATIN2, LATIN9, and WIN1250 encodings. (See the unaccent module for another, more flexible solution.)
to_ascii('Karél') → Karel
to_bin ( integer ) → text
to_bin ( bigint ) → text
Converts the number to its equivalent two's complement binary representation.
to_bin(2147483647) → 1111111111111111111111111111111
to_bin(-1234) → 11111111111111111111101100101110
to_hex ( integer ) → text
to_hex ( bigint ) → text
Converts the number to its equivalent two's complement hexadecimal representation.
to_hex(2147483647) → 7fffffff
to_hex(-1234) → fffffb2e
to_oct ( integer ) → text
to_oct ( bigint ) → text
Converts the number to its equivalent two's complement octal representation.
to_oct(2147483647) → 17777777777
to_oct(-1234) → 37777775456
translate ( string text, from text, to text ) → text
Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.
translate('12345', '143', 'ax') → a2x5
unistr ( text ) → text
Evaluate escaped Unicode characters in the argument. Unicode characters can be specified as \_`XXXX`_ (4 hexadecimal digits), \+_`XXXXXX`_ (6 hexadecimal digits), \u_`XXXX`_ (4 hexadecimal digits), or \U_`XXXXXXXX`_ (8 hexadecimal digits). To specify a backslash, write two backslashes. All other characters are taken literally.
If the server encoding is not UTF-8, the Unicode code point identified by one of these escape sequences is converted to the actual server encoding; an error is reported if that's not possible.
This function provides a (non-standard) alternative to string constants with Unicode escapes (see Section 4.1.2.3).
unistr('d\0061t\+000061') → data
unistr('d\u0061t\U00000061') → data