This section describes functions and operators for examining and manipulating string values. Strings in this context include values of all 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. Details are in Table 4-6. These functions are also implemented using the regular syntax for function invocation. (See Table 4-7.)
Table 4-6. SQL String Functions and Operators
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| string || string | text | string concatenation | 'Postgre' || 'SQL' | PostgreSQL |
| char_length(string) or character_length(string) | integer | length of string | char_length('jose') | 4 |
| lower(string) | text | Convert string to lower case. | lower('TOM') | tom |
| octet_length(string) | integer | number of bytes in string | octet_length('jose') | 4 |
| position(substring in string) | integer | location of specified substring | position('om' in 'Thomas') | 3 |
| substring(string [from integer] [for integer]) | text | extract substring | substring('Thomas' from 2 for 3) | oma |
| trim([leading | trailing | both] [characters] from string) | 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 'xTomx') | Tom |
| upper(string) | 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 4-7. Other String Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| ascii(text) | integer | Returns the ASCII code of the first character of the argument. | ascii('x') | 120 |
| btrim(string text, trim text) | 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) | text | Returns the character with the given ASCII code. | chr(65) | A |
| initcap(text) | text | Converts first letter of each word (whitespace separated) to upper case. | initcap('hi thomas') | Hi Thomas |
| lpad(string text, length integer [, fill text]) | 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) | text | Removes the longest string containing only characters from trim from the start of the string. | ltrim('zzzytrim','xyz') | trim |
| repeat(text, integer) | text | Repeat text a number of times. | repeat('Pg', 4) | PgPgPgPg |
| rpad(string text, length integer [, fill text]) | 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) | text | Removes the longest string containing only characters from trim from the end of the string. | rtrim('trimxxxx','x') | trim |
| strpos(string, substring) | text | Locates specified substring. (same as position(substring in string), but note the reversed argument order) | strpos('high','ig') | 2 |
| substr(string, from [, count]) | text | Extracts specified substring. (same as substring(string from from for count)) | substr('alphabet', 3, 2) | ph |
| to_ascii(text [, encoding]) | text | Converts text from multibyte encoding to ASCII. | to_ascii('Karel') | Karel |
| translate(string text, from text, to text) | 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 |
The to_ascii function supports conversion from LATIN1, LATIN2, WIN1250 (CP1250) only.