String Functions

I l @ ve RuBoard

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 
I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net