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

FunctionReturnsDescriptionExample
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

FunctionReturnsDescriptionExample
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.