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-4. SQL92 String Functions

FunctionReturnsDescriptionExample
char_length(string)int4length of stringchar_length('jose')
character_length(string)int4length of stringchar_length('jose')
lower(string)stringconvert string to lower caselower('TOM')
octet_length(string)int4storage length of stringoctet_length('jose')
position(string in string)int4location of specified substringposition('o' in 'Tom')
substring(string [from int] [for int])stringextract specified substringsubstring('Tom' from 2 for 2)
trim([leading|trailing|both] [string] from string)stringtrim characters from stringtrim(both 'x' from 'xTomx')
upper(text)textconvert text to upper caseupper('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-5. String Functions

FunctionReturnsDescriptionExample
char(text)charconvert text to char typechar('text string')
char(varchar)charconvert varchar to char typechar(varchar 'varchar string')
initcap(text)textfirst letter of each word to upper caseinitcap('thomas')
lpad(text,int,text)textleft pad string to specified lengthlpad('hi',4,'??')
ltrim(text,text)textleft trim characters from textltrim('xxxxtrim','x')
textpos(text,text)textlocate specified substringposition('high','ig')
rpad(text,int,text)textright pad string to specified lengthrpad('hi',4,'x')
rtrim(text,text)textright trim characters from textrtrim('trimxxxx','x')
substr(text,int[,int])textextract specified substringsubstr('hi there',3,5)
text(char)textconvert char to text typetext('char string')
text(varchar)textconvert varchar to text typetext(varchar 'varchar string')
translate(text,from,to)textconvert character in stringtranslate('12345', '1', 'a')
varchar(char)varcharconvert char to varchar typevarchar('char string')
varchar(text)varcharconvert text to varchar typevarchar('text string')

Most functions explicitly defined for text will work for char() and varchar() arguments.