Unsupported versions: 6.4
Historical version. PostgreSQL 6.4 has reached end of support. See the current manual.
String Functions
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.
Report a documentation issue
Read the upstream documentation. For corrections, first check the current manual.