SQL92 defines string functions with specific syntax. Some of these are implemented using other Postgres functions.
Table 10-2. SQL92 String Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| position(text in text) | int4 | location of specified substring | position('o' in 'Tom') |
| substring(text [from int] [for int]) | text | extract specified substring | substring('Tom' from 2 for 2) |
| trim([leading|trailing|both] [text] from text) | text | trim characters from text | trim(both 'x' from 'xTomx') |
Many string functions are available for text, varchar(), and char() types. Some are used internally to implement the SQL92 string functions listed above.
Table 10-3. String Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| char(text) | char | convert text to char type | char('text string') |
| char(varchar) | char | convert varchar to char type | char(varchar 'varchar string') |
| initcap(text) | text | first letter of each word to upper case | initcap('thomas') |
| lower(text) | text | convert text to lower case | lower('TOM') |
| lpad(text,int,text) | text | left pad string to specified length | lpad('hi',4,'??') |
| ltrim(text,text) | text | left trim characters from text | ltrim('xxxxtrim','x') |
| position(text,text) | text | extract specified substring | position('high','ig') |
| rpad(text,int,text) | text | right pad string to specified length | rpad('hi',4,'x') |
| rtrim(text,text) | text | right trim characters from text | rtrim('trimxxxx','x') |
| substr(text,int[,int]) | text | extract specified substring | substr('hi there',3,5) |
| text(char) | text | convert char to text type | text('char string') |
| text(varchar) | text | convert varchar to text type | text(varchar 'varchar string') |
| translate(text,from,to) | text | convert character in string | translate('12345', '1', 'a') |
| varchar(char) | varchar | convert char to varchar type | varchar('char string') |
| varchar(text) | varchar | convert text to varchar type | varchar('text string') |
| upper(text) | text | convert text to upper case | upper('tom') |
Most functions explicitly defined for text will work for char() and varchar() arguments.