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.