Unsupported versions: 6.5
Historical version. PostgreSQL 6.5 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. The supported string types for SQL92 are char, varchar, and text.
Table 5-3. SQL92 String Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| char_length(string) | int4 | length of string | char_length('jose') |
| character_length(string) | int4 | length of string | char_length('jose') |
| lower(string) | string | convert string to lower case | lower('TOM') |
| octet_length(string) | int4 | storage length of string | octet_length('jose') |
| position(string in string) | int4 | location of specified substring | position('o' in 'Tom') |
| substring(string [from int] [for int]) | string | extract specified substring | substring('Tom' from 2 for 2) |
| trim([leading|trailing|both] [string] from string) | string | trim characters from string | trim(both 'x' from 'xTomx') |
| upper(text) | text | convert text to upper case | upper('tom') |
Many additional string functions are available for text, varchar(), and char() types. Some are used internally to implement the SQL92 string functions listed above.
Table 5-4. 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') |
| 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') |
| textpos(text,text) | text | locate 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') |
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.