This section mainly discusses some string operation functions. See the table below for details.
Function | Returned values | Description | Sample code | Result |
string || string | text | String concatenation | 'Post' || 'greSQL' | PostgreSQL |
string || non-string or non-string || string | text | Concatenation between strings and non-strings | 'Value: ' || 42 | Value: 42 |
bit_length(string) | int | Number of bits in a string | bit_length('jose') | 32 |
char_length(string) or character_length(string) | int | Character count of a string | char_length('jose') | 4 |
lower(string) | text | Convert to lowercase characters | lower('TOM') | tom |
octet_length(string) | int | Byte count of a string | octet_length('jose') | 4 |
overlay(string placing string from int [for int]) | text | Replace substring | overlay('Txxxxas' placing 'hom' from 2 for 4) | Thomas |
position(substring in string) | int | Position of substring in string | position('om' in 'Thomas') | 3 |
substring(string [from int] [for int]) | text | Extract substring | substring('Thomas' from 2 for 3) | hom |
substring(string from pattern) | text | Regular expression matching of substring | substring('Thomas' from '...$') | mas |
trim([leading | trailing | both] [characters] fromstring) | text | Remove substring | trim(both 'x' from 'xTomxx') | Tom |
upper(string) | text | Convert to uppercase letters | upper('tom') | TOM |
The database also provides some other string functions to support string manipulation. See the table below.
Function | Returned values | Feature | Sample code | Result |
ascii(string) | int | Get the ASCII code of the first character | ascii('x') | 120 |
btrim(string text [, characters text]) | text | Remove the longest sequence from the beginning and end (but not the middle) of the string that only contains characters from characters (default is whitespace) | btrim('xyxtrimyyx', 'xy') | trim |
chr(int) | text | Convert ASCII code to character | chr(65) | A |
convert(string bytea, src_encodingname, dest_encoding name) | bytea | Change encoding using the specified conversion name | convert('text_in_utf8', 'UTF8', 'LATIN1') | - |
convert_from(string bytea,src_encoding name) | text | Convert to the database's default encoding format | convert_from('text_in_utf8', 'UTF8') | text_in_utf8represented in the current database encoding |
convert_to(string text, dest_encodingname) | bytea | Convert data to a specified encoding format | convert_to('some text', 'UTF8') | - |
decode(string text, type text) | bytea | Decoding function | decode('MTIzAAE=', 'base64') | 123\000\001 |
encode(data bytea, type text) | text | Encoding function | encode(E'123\000\001', 'base64') | MTIzAAE= |
initcap(string) | text | Capitalize the first letter of each word, with the rest in lowercase | initcap('hi THOMAS') | Hi Thomas |
length(string) | int | String length | length('jose') | 4 |
length(stringbytea, encoding name ) | int | Length of the string in the specified encoding format | length('jose', 'UTF8') | 4 |
ltrim(string text [, characters text]) | text | Remove the longest sequence from the beginning (but not the middle) of the string that only contains characters from characters (default is whitespace) | ltrim('zzzytrim', 'xyz') | trim |
md5(string) | text | Compute the MD5 value of the string | md5('abc') | 900150983cd24fb0 d6963f7d28e17f72 |
pg_client_encoding() | name | Get the client's encoding format | pg_client_encoding() | SQL_ASCII |
regexp_replace(string text, patterntext, replacement text [, flags text]) | text | - | regexp_replace('Thomas', '.[mN]a.', 'M') | ThM |
repeat(string text, n int) | text | Repeat the string n times | repeat('Pg', 4) | PgPgPgPg |
replace(string text, from text, totext) | text | Replace string | replace('abcdefabcdef', 'cd', 'XX') | abXXefabXXef |
rtrim(string text [, characters text]) | text | Remove the longest sequence from the end (but not the middle) of the string that only contains characters from characters (default is whitespace) | rtrim('trimxxxx', 'x') | trim |
strpos(string, substring) | int | Get the position of the substring | strpos('high', 'ig') | 2 |
substr(string, from [, count]) | text | Get substring | substr('alphabet', 3, 2) | ph |
to_ascii(string text [, encodingtext]) | text | Convert to ASCII code format | to_ascii('Karel') | Karel |
to_hex(number int or bigint) | text | Convert number to hexadecimal | to_hex(2147483647) | 7fffffff |