String Functions

PostgreSQL includes several functions that modify string-related data. These functions are particularly useful for controlling output displays and/or normalizing data input.

ASCII

Description

The ASCII function returns the ASCII value for the supplied character.

Inputs

ASCII(chr) 

chr—The character to determine the ASCII value of.

Examples

ASCII('A')  65 
ASCII('Apple')  65 

Notes

In the case of multiple characters being supplied to the ASCII function, only the first is evaluated.

CHR

Description

The CHR function returns the character that corresponds to the ASCII value provided.

Inputs

CHR(val) 

val—An ASCII value.

Example

CHR(65)  'A' 

INITCAP

Description

The INITCAP function forces a string or column to be returned when the first character is uppercase and the rest is lowercase only.

Inputs

INITCAP(col) 

Or

INITCAP(string) 

Example

SELECT INITCAP(name) AS Proper_Name FROM authors; 

Proper_Name 
--------
Bill 
Bob 
Sam 

LENGTH, CHAR_LENGTH, or CHARACTER_LENGTH

Description

The LENGTH ( or CHAR_LENGTH, or CHARACTER_LENGTH) function returns the length of the supplied column.

Inputs

LENGTH(col) 

col—A column containing a string data type.

Example

SELECT name WHERE LENGTH(name)<4 FROM authors; 

Name 
------
Pam 
Sam 
Sue 
Bob 

LOWER

Description

The LOWER function forces a string or column to be returned in lowercase only.

Inputs

LOWER(col) 

Or

LOWER(string) 

Example

SELECT LOWER(name) AS Low_Name FROM authors; 

Low_Name 
--------
bill 
bob 
sam 

LPAD

Description

The LPAD function left-pads a string with specified characters or spaces.

Inputs

LPAD(str, len, fill) 

str—The string to left-pad.

len—The number of spaces to pad.

fill—By default, a space; however, any characters can be specified.

Examples

LPAD('Hello', 3) ' Hello' 
LPAD('ello', 3, 'H')  'HHHello' 

LTRIM

Description

The LTRIM function removes the specified characters from the left side of a character string.

Inputs

LTRIM(str [,trim]) 

str—The string to trim.

trim—By default, a space; however, any character(s) can be specified.

Examples

LTRIM('   Hello')  'Hello' 
LTRIM('HHHello', 'H')  'ello' 

OCTET_LENGTH

Description

The OCTET_LENGTH function returns the length of a column or string, including any multibyte data present.

Inputs

OCTET_LENGTH(col) 

Or

OCTET_LENGTH(string) 

Example

SELECT OCTET_LENGTH('Hello World'); 

Octet_Length 
11 

Notes

OCTET_LENGTH and LENGTH will often return the same value. However, a crucial difference is that OCTET_LENGTH is actually returning the number of bytes in a string. This can be an important difference if multibyte information is being stored.

POSITION

Description

The POSITION function returns an integer that represents the position of the supplied character string in the given column (or supplied string).

Inputs

POSITION(str IN col) 

str—The character string to locate.

col—The column or string to perform the search on.

Example

Return the names from the table authors where the second letter is an 'a':

SELECT name FROM authors WHERE POSITION('a' IN name))=2; 

Name 
------
Pam 
Sam 
Tammy 
Barry 

STRPOS

Description

The STRPOS function returns an integer that represents the position of a specific character string in a given column (or supplied string).

Inputs

STRPOS(col, str) 

col—The column or string to perform the search on.

str—The character string to locate.

Example

See the examples in the POSITION function section.

Notes

This command is essentially the same as the POSITION function.

RPAD

Description

The RPAD function right-fills the specified string with spaces or characters.

Inputs

RPAD(str, len [,fill]) 

str—The string to right-fill.

len—The number of spaces to append.

fill—By default, a space; however, any character can be used.

Examples

RPAD('Hello', 3)  'Hello   ' 
RPAD('Hello', 3, '!')  Hello!!! 

RTRIM

Description

The RTRIM function removes the specified characters from the right side of a character string.

Inputs

RTRIM(str [,trim]) 

str—The string to right-trim.

trim—By default, a space; however, any character(s) can be used.

Examples

RTRIM('Hello   ')  'Hello' 
RTRIM('Hello!!!', '!')  'Hello' 

SUBSTRING

Description

The SUBSTRING function extracts a specified portion from an existing character string.

Inputs

SUBSTRING(str FROM pos [ FOR len]) 

str—The string to manipulate.

pos—The starting position to begin extraction.

len—By default, the rest of the string is assumed; however, a specific portion can be specified.

Examples

SUBSTRING('Hello' FROM 2)  'ello' 
SUBSTRING('Hello' FROM 2 FOR 2)  'el' 

Notes

This is the same as the SUBSTR function.

SUBSTR

Description

The SUBSTR function extracts a specified portion from an existing character string.

Inputs

SUBSTRING(str, pos [, len]) 

str—The string to manipulate.

pos—The starting position to begin extraction.

len—By default, the rest of the string is assumed; however, a specific portion can be specified.

Examples

SUBSTRING('Hello', 2)  'ello' 
SUBSTRING('Hello', 2, 2)  'el' 

Notes

This is the same as the SUBSTRING function.

TRANSLATE

Description

The TRANSLATE function performs a search and replace on a specified string. The data replaced is done according to where it matches in the search criteria. See the following example for more.

Inputs

TRANSLATE(str, searchset, replaceset) 

str—The base string to search and modify.

searchset—Either a single character or a multicharacter search set.

replaceset—Each respective member in this set replaces a corresponding member in the search set.

Examples

TRANSLATE('HelloW', 'W', '!')  'Hello!' 
TRANSLATE('Hello', 'Ho', 'Jy')  'Jelly' 

TRIM

Description

The TRIM function removes the specified character or whitespace from the left or right (or both) of a given string.

Inputs

TRIM([ leading | trailing | both ] [trim] FROM str) 

leading | trailing | both— The side from which to remove the specified characters.

trim—By default, whitespace is assumed; however, any character(s) can be specified.

str—The string to trim.

Examples

TRIM(both FROM '  Hello   ')  'Hello' 
TRIM(both '!' FROM '!!HELLO!!')  'Hello' 

UPPER

Description

The UPPER function forces a string or column to be returned in uppercase only.

Inputs

UPPER(col) 

Or

UPPER(string) 

Example

SELECT UPPER(name) AS Upper_Name FROM authors; 

Upper_Name 
--------
BILL 
BOB 
SAM