This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types CHARACTER, CHARACTER VARYING, and TEXT. Unless otherwise noted, all of the functions listed below work on all of these types, but be wary of potential effects of the automatic padding when using the CHARACTER type. Generally the functions described here also work on data of non-string types by converting that data to a string representation first. Some functions also exist natively for bit string types.
SQL defines some string functions with a special syntax where certain keywords rather than commas are used to separate the arguments. See Table 3-7 for details. These functions are also implemented using the regular syntax for function invocation. (See Table 3-8.)
Table 3-7. SQL String Functions and Operators
| Function | Description | Example | Result |
|---|---|---|---|
| string || string Returns text | String concatenation | 'Postgre' || 'SQL' | PostgreSQL |
| bit_length(string) Returns integer | Number of bits in string | bit_length('jose') | 32 |
| char_length(string) or character_length(string) Returns integer | Number of characters in string | char_length('jose') | 4 |
| lower(string) Returns text | Convert string to lower case | lower('TOM') | tom |
| octet_length(string) Returns integer | Number of bytes in string | octet_length('jose') | 4 |
| position(substring in string) Returns integer | Location of specified substring | position('om' in 'Thomas') | 3 |
| substring(string from integer for integer) Returns text | Extract substring | substring('Thomas' from 2 for 3) | hom |
| trim(leading | trailing | both characters from string) Returns text | Removes the longest string containing only the characters (a space by default) from the beginning/end/both ends of the string. | trim(both 'x' from 'xTomxx') | Tom |
| upper(string) Returns text | Convert string to upper case | upper('tom') | TOM |
Additional string manipulation functions are available and are listed below. Some of them are used internally to implement the SQL string functions listed above.
Table 3-8. Other String Functions
| Function | Description | Example | Result |
|---|---|---|---|
| ascii(text) Returns integer | Returns the ASCII code of the first character of the argument. | ascii('x') | 120 |
| btrim(string text, trim text) Returns text | Remove (trim) the longest string consisting only of characters in trim from the start and end of string. | btrim('xyxtrimyyx','xy') | trim |
| chr(integer) Returns text | Returns the character with the given ASCII code. | >chr(65) | A |
| convert(string text, [src_encoding name,] dest_encoding name) Returns text | Converts string using dest_encoding. The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed. | convert('text_in_unicode', 'UNICODE', 'LATIN1') | text_in_unicode represented in ISO 8859-1 |
| initcap(text) Returns text | Converts first letter of each word (whitespace separated) to upper case. | initcap('hi thomas') | Hi Thomas |
| length(string) Returns integer | length of string | length('jose') | 4 |
| lpad(string text, length integer [, fill text]) Returns text | Fills up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). | lpad('hi', 5, 'xy') | xyxhi |
| ltrim(string text, trim text) Returns text | Removes the longest string containing only characters from trim< from the start of the string. | ltrim('zzzytrim','xyz') | trim |
| pg_client_encoding() Returns name | Returns current client encoding name. | pg_client_encoding() | SQL_ASCII |
| repeat(text, integer) Returns text | Repeat text a number of times. | repeat('Pg', 4) | PgPgPgPg |
| rpad(string text, length integer [, fill text]) Returns text | Fills up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. | rpad('hi', 5, 'xy') | hixyx |
| rtrim(string text, trim text) Returns text | Removes the longest string containing only characters from trim from the end of the string. | rtrim('trimxxxx','x') | trim |
| strpos(string, substring) Returns text | Locates specified substring. (Same as position(substring in string) but note the reversed argument order.) | strpos('high','ig') | 2 |
| substr(string, from [, count]) Returns text | Extracts specified substring. (Same as substring(string from from for count)) | substr('alphabet', 3, 2) | ph |
| to_ascii(text [, encoding]) Returns text | Converts text from multibyte encoding to ASCII. | to_ascii('Karel') | Karel |
| translate(string text, from text, to text) Returns text | Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. | translate('12345', '14', 'ax') | a23x5 |
| encode(data bytea, type text) Returns text | Encodes binary data to ASCII-only representation. Supported types are: 'base64', 'hex', 'escape'. | encode('123\\000\\001', 'base64') | MTIzAAE= |
| decode(string text, type text) Returns bytea | Decodes binary data from string previously encoded with encode(). Parameter type is same as in encode(). | decode('MTIzAAE=', 'base64') | 123\000\001 |
The to_ascii function supports conversion from LATIN1, LATIN2, WIN1250 (CP1250) only.