30th September 2021: PostgreSQL 14 Released!
Unsupported versions: 6.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

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.