This section describes functions and operators for examining
and manipulating string values. Strings in this context include
values of all the types CHARACTER,
CHARACTER VARYING, and TEXT. Unless otherwise noted, all of the functions
listed below work on all of these types, but be wary of potential
effects of the automatic padding when using the CHARACTER type. Generally, the functions described
here also work on data of non-string types by converting that
data to a string representation first. Some functions also exist
natively for bit-string types.

SQL defines some string
functions with a special syntax where certain keywords rather
than commas are used to separate the arguments. Details are in
Table
4-6. These functions are also implemented using the regular
syntax for function invocation. (See Table
4-7.)

Table 4-6. SQL
String Functions and Operators

Function

Return Type

Description

Example

Result

string||string

text

string concatenation

'Postgre' || 'SQL'

PostgreSQL

bit_length(string)

integer

number of bits in string

bit_length('jose')

32

char_length(string)
or character_length(string)

integer

number of characters in string

char_length('jose')

4

lower(string)

text

Convert string to lower case.

lower('TOM')

tom

octet_length(string)

integer

number of bytes in string

octet_length('jose')

4

position(substring in string)

integer

location of specified substring

position('om' in
'Thomas')

3

substring(string [from
integer] [for integer])

text

extract substring

substring('Thomas' from 2 for
3)

hom

trim([leading | trailing | both]
[characters] from string)

text

Removes the longest string containing only the
characters (a space by
default) from the beginning/end/both ends of the
string.

trim(both 'x' from
'xTomxx')

Tom

upper(string)

text

Convert string to upper case.

upper('tom')

TOM

Additional string manipulation functions are available and are
listed below. Some of them are used internally to implement the
SQL-standard string functions
listed above.

Table 4-7. Other String Functions

Function

Return Type

Description

Example

Result

ascii(text)

integer

Returns the ASCII
code of the first character of the argument.

ascii('x')

120

btrim(stringtext,
trimtext)

text

Remove (trim) the longest string consisting only of
characters in trim from
the start and end of string.

btrim('xyxtrimyyx','xy')

trim

chr(integer)

text

Returns the character with the given ASCII code.

chr(65)

A

convert(stringtext,
[src_encodingname,] dest_encodingname)

text

Converts string using dest_encoding. The original encoding
is specified by src_encoding. If src_encoding is omitted, database
encoding is assumed.

convert('text_in_unicode',
'UNICODE', 'LATIN1')

text_in_unicode represented
in ISO 8859-1

initcap(text)

text

Converts first letter of each word (whitespace
separated) to upper case.

initcap('hi thomas')

Hi Thomas

length(string)

integer

length of string

length('jose')

4

lpad(stringtext,
lengthinteger [,
filltext])

text

Fills up the string to
length length by
prepending the characters fill (a space by default). If the
string is already longer
than length then it is
truncated (on the right).

lpad('hi', 5, 'xy')

xyxhi

ltrim(stringtext,
trimtext)

text

Removes the longest string containing only characters
from trim from the start
of the string.

ltrim('zzzytrim','xyz')

trim

pg_client_encoding()

name

Returns current client encoding name.

pg_client_encoding()

SQL_ASCII

repeat(text, integer)

text

Repeat text a number of times.

repeat('Pg', 4)

PgPgPgPg

rpad(stringtext,
lengthinteger [,
filltext])

text

Fills up the string to
length length by appending
the characters fill (a
space by default). If the string is already longer than
length then it is
truncated.

rpad('hi', 5, 'xy')

hixyx

rtrim(string text, trim text)

text

Removes the longest string containing only characters
from trim from the end of
the string.

Comments

June 19, 2002, 2:32 a.m.

If you want to concatenate a string with a numeric variable field, you cannot simply make a casting of numeric var to string, because it's not allowed. You should first make a casting of numeric var to integer, and then postgresql itself makes the casting of integer to string. Here's an example:

if string_field = 'AAA' and num_var = 23 then

table.string_field || CAST(table.num_var AS INTEGER)

returns 'AAA23'

Aug. 3, 2002, 9:31 p.m.

@Jackson:NULL is not ''. As a general rule, any operator will return NULL if one of it's arguments is NULL. You can use COALESCE(var,'') to force the behaviour you want.

Nov. 6, 2002, 8:03 p.m.

Here's a useful function to replace within a string:

/*replace all occurences of 'match' in 'subject' with 'replace'. Result is returned*/