Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Character String Functions

PostgreSQL supports a wide variety of text formatting, analysis and comparison functions. These include both SQL92 standard functions, such as substring() and trim(), as well as PostgreSQL-specific extensions, such as ltrim(), rtrim() and substr(). Table 5-10 lists the functions available to PostgreSQL for use with character strings. In general, when referring to a value of type text, it is functionally synonymous with a value of type character, or varchar.

Table 5-10. Character string functions

Function

Description

ascii( s  )

Returns the ascii code of the first character passed to it in character string s

btrim( s   [, t  ])

Returns character string s , trimmed on the left and right of any substrings consisting solely of letters in character string t (or whitespace, if t is not specified)

char_length( s  )

Returns the numeric length of character string s

chr( n  )

Returns the character whose ascii value corresponds to the number n

s   ilike( f  )

Returns true if the expression f is found to match (case-insensitively) s

initcap( s  )

Returns the character string s , with each word's first letter capitalized

length( s  )

Returns the numeric length of character string s

s   like( f  )

Returns true if the expression f is found to match s

lower( s  )

Returns the string s , in all lowercase

lpad( s  , n   [, c  ])

Returns the character string s , padded to the left with character string c (or whitespace, if c is not defined to length of n characters (or truncated on the right to n characters)

ltrim( s   [, f  ])

Returns character string s , trimmed on the left of a substring consisting solely of letters in character string f (or whitespace, if f is not specified)

octet_length( s  )

Returns the number of 8-bit bytes in character string s

position( b IN s  )

Returns the location of character sub-string b in character string s (counting from 1)

repeat( s  , n  )

Returns the character string s , repeated n times

rpad( s  , n   [, c  ])

Returns the character string s , padded to the right with character string c (or whitespace, if c is not specified) to length of n characters (or truncated on the left to n characters)

rtrim( s   [, f  ])

Returns character string s , trimmed on the right of a substring consisting solely of letters in character string f (or whitespace, if f is not specified)

strpos( s  , b  )

Returns the location of character sub-string b in character string s (counting from 1). This is a PostgreSQL specific function which duplicates the effect of the SQL position() function, using C style arguments.

substr( s  , n [, l  ])

Returns a character sub-string of the character string s , starting at digit n (counting from 1), with optional maximum length l characters

substring( s FROM n FOR l  )

Returns a character sub-string of the character string s , starting at digit n (counting from 1), with optional maximum length l characters

to_ascii( s  , f  )

Returns text s converted from multibyte encoding format f to plain ASCII

translate( s  , f  , r  )

Returns the character string s , with any found characters from string f replaced with corresponding character in string r

trim( side f FROM s )

Returns character string s , trimmed of leading and/or trailing substrings which consist solely of letters in character string f , as dictated by the side keyword (which is either LEADING, TRAILING or BOTH)

upper( s  )

Returns the character string s , converted to all uppercase

The following sections describe each of these character string functions, detailing their argument requirements, return types, and general usage.

ascii()

ascii(
s
 )

The ascii() function accepts a single argument of either a single character, or a character string of type text, and returns the numeric ASCII value of the first character interpreted. The result is returned as a value of type integer.

Examples

booktown=# 
SELECT ascii('T');

 ascii
-------
    84
(1 row)

booktown=# 
SELECT DISTINCT ON (substr)

booktown-# 
       title, substr(title, 1, 1),

booktown-# 
       ascii(title)

booktown-# 
       FROM books

booktown-# 
       ORDER BY substr ASC;

            title            | substr | ascii
-----------------------------+--------+-------
 2001: A Space Odyssey       | 2      |    50
 Bartholomew and the Oobleck | B      |    66
 Dune                        | D      |    68
 Franklin in the Dark        | F      |    70
 Goodnight Moon              | G      |    71
 Little Women                | L      |    76
 Practical PostgreSQL        | P      |    80
 The Shining                 | T      |    84
(8 rows)

btrim()

btrim(
s
 )
btrim(
s
 , 
t
 )

The btrim() function accepts one or two arguments s , and (optionally) t , each of type text. If t is specified, the function trims the string value s of any leading or trailing strings consisting solely of characters described in t . If t is not specified, leading and trailing whitespace is trimmed. The resultant trimmed value is returned as type text.

It is important to understand that the order of the characters described by t is not relevant to btrim(). Any strings at the beginning or end of s that consecutively match any of the characters described in t will be trimmed.

Example

booktown=# 
SELECT btrim('  whitespace example   ') AS trim_blanks,

booktown-# 
       btrim('123example 332', '123') AS trim_numbers;

     trim_blanks     | trim_numbers
---------------------+--------------
 whitespace example | example
(1 row)

char_length()

char_length(
s
 )

The char_length() SQL92 function accepts a single argument of type text, varchar, or character, and returns the number of characters in the character string s passed to it. The returned value is of type integer.

Example

booktown=# 
SELECT char_length(title), title

booktown-# 
       FROM books

booktown-# 
       LIMIT 3;

 char_length |         title
-------------+-----------------------
          11 | The Shining
           4 | Dune
          21 | 2001: A Space Odyssey
(3 rows)

chr()

chr(
n
 )

The chr() function accepts a single numeric argument n of type integer, and returns the corresponding character value for that ASCII value of n . The resultant value is of type text.

The chr() function is effectively the inverse of the ascii function.

Examples

booktown=# 
SELECT chr(65), ascii('A');

 chr | ascii
-----+-------
 A   |    65
(1 row)

initcap()

initcap(
s
 )

The initcap() function accepts a single argument s of type text, and returns its value, with the first letter of each word capitalized. In this context, a "word" is a string of characters separated from other words by whitespace.

Example

booktown=# 
SELECT initcap('a prospective book title');

         initcap
--------------------------
 A Prospective Book Title
(1 row)

length()

length(
s
 )

Functionally identical to the char_length() SQL92 function. Accepts a single argument s of type text, character, or varchar, and returns its length as a value of type integer.

Example

booktown=# 
SELECT length(title), title

booktown-# 
       FROM books

booktown-# 
       LIMIT 3;

 length |         title
--------+-----------------------
     11 | The Shining
      4 | Dune
     21 | 2001: A Space Odyssey
(3 rows)

Note: The length evaluation functions for character strings defined in SQL92 are char_length() and octet_length(). Therefore, these functions are more likely to exist within other RDBMS systems than the length() function.

like() and ilike()


s
 like(
f
 )

s
 LIKE 
f

like(
s
 , 
f
 )

s
 ilike(
f
 )

s
 ILIKE 
f

The like() function checks the expression described by f , and attempts to see if it matches the character string s . It may either accept two arguments of type text, s and f , or it may be used in a special SQL syntax format where the argument s precedes the function name, adding to the readability of the statement. The ilike() function is a non-standard, case-insensitive version of like(), and may only be invoked through the SQL-style syntax.

Note: The SQL keyword LIKE actually invokes the like() function with PostgreSQL. The ability to use the LIKE keyword without parentheses to invoke this functionality is a syntactic convenience, and there is no different in practice.

The use of like() differs from a normal equivalence operation in that the character string f may contain either an underscore ( _ ) or percent (%) symbol to indicate special meaning in matching character values. PostgreSQL interprets the _ symbol as indicating that any single character should be considered a match, while the % symbol is interpreted as indicating that zero or more characters of any value will be considered a match. These special characters may be interspersed throughout the character string f .

For more advanced pattern matching capabilities within PostgreSQL, see the Section called Regular expression matching operators " earlier in this chapter.

Examples

booktown=# 
SELECT * FROM books

booktown-# 
       WHERE title LIKE ('%Rabbit');

  id  |        title         | author_id | subject_id
------+----------------------+-----------+------------
 1234 | The Velveteen Rabbit |     25041 |          3
(1 row)

booktown=# 
SELECT * FROM books

booktown-# 
       WHERE title LIKE '%D___';

  id   |        title         | author_id | subject_id
-------+----------------------+-----------+------------
  4513 | Dune                 |      1866 |         15
 25908 | Franklin in the Dark |     15990 |          2
(2 rows)

booktown=# 
SELECT * FROM books

booktown-# 
       WHERE title ILIKE '%python%';

  id   |       title        | author_id | subject_id
-------+--------------------+-----------+------------
 41473 | Programming Python |      7805 |          4
 41477 | Learning Python    |      7805 |          4
(2 rows)

lower()

lower(
s
 )

The lower() SQL92 function accepts a single character string argument s of type text, and returns the same value with all characters converted to lowercase. The resultant value is returned as type text.

Example

booktown=# 
SELECT lower(title)

booktown-# 
       FROM books

booktown-# 
       LIMIT 3;

         lower
-----------------------
 the shining
 dune
 2001: a space odyssey
(3 rows)

lpad()

lpad(
s
 , 
n
 )
lpad(
s
 , 
n
 , 
c
 )

The lpad() function accepts either two or three arguments s , n , and optionally c , of types text, integer, and text, respectively. The function "pads" the left side of the character string s with either whitespace, or the optional character string defined by c , until it is exactly n characters in length.

If the character string s is longer than n characters to begin with, s will be truncated from the right until it is exactly n characters in length.

Example

booktown=# 
SELECT title, lpad(title, 12, '-') AS dashed,

booktown-# 
       lpad(title, 12, '-+-') AS plus_dashed

booktown-# 
       FROM books

booktown-# 
       LIMIT 4;

         title         |    dashed    | plus_dashed
-----------------------+--------------+--------------
 The Shining           | -The Shining | -The Shining
 Dune                  | --------Dune | -+--+--+Dune
 2001: A Space Odyssey | 2001: A Spac | 2001: A Spac
 The Cat in the Hat    | The Cat in t | The Cat in t
(4 rows)

ltrim()

ltrim(
s
 )
ltrim(
s
 , 
f
 )

The ltrim() function accepts either one or two arguments, s and optionally f , each of type text. If f is unspecified, the function returns the value of s , with any leading whitespace trimmed off. Otherwise, the function returns the character string s , with any leading substring containing exclusively characters contained in f removed. If no such substring is found, no change is made.

Examples

booktown=# 
SELECT ltrim('    whitespace example');

       ltrim
--------------------
 whitespace example
(1 row)

booktown=# 
SELECT title, ltrim(title, 'TD2he ')

booktown-# 
       FROM books

booktown-# 
       LIMIT 4;

         title         |        ltrim
-----------------------+----------------------
 The Shining           | Shining
 Dune                  | une
 2001: A Space Odyssey | 001: A Space Odyssey
 The Cat in the Hat    | Cat in the Hat
(4 rows)

octet_length()

char_length(
s
 )

The octet_length() SQL92 function accepts a single argument of type text, varchar or, character, and returns the number of 8-bit character bytes in the character string s passed to it. The returned value is of type integer.

In most circumstances, there will be the same number of octets as there are characters to a character string, though this may not necessarily be the case with multibyte characters. This is because a multibyte character may consist of more than a single octet (byte), by definition.

Example

booktown=# 
SELECT title, octet_length(title)

booktown-# 
       FROM books

booktown-# 
       ORDER BY title ASC

booktown-# 
       LIMIT 3;

            title            | octet_length
-----------------------------+--------------
 2001: A Space Odyssey       |           21
 Bartholomew and the Oobleck |           27
 Dune                        |            4
(3 rows)

position()

position(
b
 IN 
s
)

The position() SQL92 function accepts two arguments, b and s , each of type text. The position of the string b within the string s is returned as a value of type integer (counting from 1). If the string is not found, zero is returned.

Example

booktown=# 
SELECT title, position('the' IN title) AS the_pos

booktown-# 
       FROM books

booktown-# 
       WHERE position('the' IN title) != 0;

            title            | the_pos
-----------------------------+---------
 The Cat in the Hat          |      12
 Bartholomew and the Oobleck |      17
 Franklin in the Dark        |      13
(3 rows)

repeat()

repeat(
s
 , 
n
 )

The repeat() function accepts two arguments s and n , of types text and integer, respectively. The function returns the character string described by s , repeated n consecutive times, as a value of type text.

Example

booktown=# 
SELECT repeat(last_name, 2)

booktown-# 
       FROM authors

booktown-# 
       LIMIT 3;

       repeat
--------------------
 DenhamDenham
 BourgeoisBourgeois
 BiancoBianco
(3 rows)

rpad()

rpad(
s
 , 
n
 )
rpad(
s
 , 
n
 , 
c
 )

The rpad() function is essentially the same as the lpad function, but operates on the right side of the string s , rather than the left. It accepts either two or three arguments s , n , and optionally c , of types text, integer, and text, respectively. The function pads the right side of the character string s with either whitespace, or the optional character string defined by c , until it is exactly n characters in length.

If the character string s is longer than n characters long to begin with, it will be truncated from the left until it is exactly n characters in length.

Examples

booktown=# 
SELECT rpad('whitespace example', 30);

              rpad
--------------------------------
 whitespace example
(1 row)

booktown=# 
SELECT title, rpad(title, 12, '-') AS right_dashed,

booktown-# 
       rpad(title, 12, '-+-') AS right_plus_dashed

booktown-# 
       FROM books

booktown-# 
       LIMIT 3;

         title         | right_dashed | right_plus_dashed
-----------------------+--------------+-------------------
 The Shining           | The Shining- | The Shining-
 Dune                  | Dune-------- | Dune-+--+--+
 2001: A Space Odyssey | 2001: A Spac | 2001: A Spac
(3 rows)

rtrim()

rtrim(
s
 )
rtrim(
s
 , 
f
 )

The rtrim() function accepts either one or two arguments, s and optionally f , each of type text. If f is unspecified, the function returns the value of s , with any trailing whitespace trimmed off. Otherwise, the function returns the character string s , with any trailing substring containing exclusively characters contained in f removed. If no such substring is found, no change is made.

Examples

booktown=# 
SELECT rtrim('whitespace example ');

       rtrim
--------------------
 whitespace example
(1 row)

booktown=# 
SELECT title, rtrim(title, 'yes')

booktown-# 
       FROM books

booktown-# 
       LIMIT 4;

         title         |        rtrim
-----------------------+----------------------
 The Shining           | The Shining
 Dune                  | Dun
 2001: A Space Odyssey | 2001: A Space Od
 The Cat in the Hat    | The Cat in the Hat
(4 rows)

strpos()

strpos(
s
 , 
b
 )

The strpos() function is functionally identical to the SQL92 position() function, but accepts C-style arguments b and s , each of type text. The position of the string b within the string s is returned as a value of type integer (counting from 1). If the string is not found, zero is returned.

Example

booktown=# 
SELECT title, strpos(lower(title), 'rabbit')

booktown-# 
       FROM books

booktown-# 
       WHERE strpos(lower(title), 'rabbit') != 0;

        title         | strpos
----------------------+--------
 The Velveteen Rabbit |     15
(1 row)

substr()

substr(
s
 , 
n
 )
substr(
s
 , 
n
 , 
l
 )

The substr() function is effectively equivalent to the SQL92 function substring(), but accepts C-style arguments s , n , and optionally l , of types text, integer, and integer, respectively. The function returns the substring of s , beginning at character index n , and optionally stopping after l characters.

If the length of the substring to be selected is longer than the available characters, only the available substring will be returned. In other words, it will not be padded as it would be with a trim function.

Example

booktown=# 
SELECT title, substr(title, 15), substr(title, 5, 9)

booktown-# 
       FROM books

booktown-# 
       ORDER BY title DESC

booktown-# 
       LIMIT 3;

        title         | substr |  substr
----------------------+--------+-----------
 The Velveteen Rabbit | Rabbit | Velveteen
 The Tell-Tale Heart  | Heart  | Tell-Tale
 The Shining          |        | Shining
(3 rows)

substring()

substring(
s
 FROM 
n
)
substring(
s
 FROM 
n
 FOR 
l
)

The substring() function is the SQL92 equivalent to the PostgreSQL-specific substr() function. It accepts two or three arguments, s , n , and optionally l , of types text, integer, and integer, respectively. The function returns the substring of s , beginning at character index n , and optionally stopping after l characters.

Examples

booktown=# 
SELECT title, substring(title FROM 15)

booktown-# 
       FROM books

booktown-# 
       ORDER BY title DESC

booktown-# 
       LIMIT 3;

        title         | substring
----------------------+-----------
 The Velveteen Rabbit | Rabbit
 The Tell-Tale Heart  | Heart
 The Shining          |
(3 rows)

booktown=# 
SELECT title, substring(title FROM 5 FOR 9)

booktown-# 
       FROM books

booktown-# 
       ORDER BY title DESC

booktown-# 
       LIMIT 3;

        title         | substring
----------------------+-----------
 The Velveteen Rabbit | Velveteen
 The Tell-Tale Heart  | Tell-Tale
 The Shining          | Shining
(3 rows)

to_ascii()

to_ascii(
s
 , 
f
 )

The to_ascii() accepts a single argument s of type text describing multibyte encoded text of the format f and returns normal ASCII text as a value of type text.

The available multibyte encoding formats are LATIN1 (ISO 8859-1), LATIN2 (ISO 8859-2), and WIN1250 (Windows CP1250, or WinLatin2). This function requires that multibyte encoding be enabled (which is a compile-time option when building and installing PostgreSQL).

Example

booktown=# 
SELECT to_ascii('Multibyte Source', 'LATIN1');

     to_ascii
-------------------
 Multibyte Source
(1 row)

translate()

translate(
s
 , 
f
 , 
r
 )

The translate() function accepts three arguments, s , f and r , each of type text. It replaces any instance of a character in the string s that matches any character in f with the corresponding character at the same index from string r . The result is returned as a value of type text.

Note that this function does not replace only complete instances of the character string f , but replaces any character within s that matches any character in f with the corresponding character from r . If there are more characters in f than in r , any character in f without a corresponding character in r will simply be omitted (this can be a useful way to remove unwanted characters).

The important thing to remember about this method of replacement is that there is always a one-to-one relationship between the character found and its replacement character (though its replacement may be empty, if omitted).

The following examples replace all question marks with exclamation points.

Examples

booktown=# 
SELECT translate('I am an example?', '?', '!');

    translate
------------------
 I am an example!
(1 row)

The next example replaces all instances of the character I with the character w , and all instances of the character s with the character a . The extra s at the end of "was" is ignored.

booktown=# 
SELECT translate('This is a mistake.', 'is', 'was');

     translate
--------------------
 Thwa wa a mwatake.
(1 row)

This final example replace all vowels with nothing, effectively removing all vowels from the input strings.

booktown=# 
SELECT title,

booktown-# 
       translate(title, 'aeiouAEIOU', '') AS vowelless

booktown-# 
       FROM books

booktown-# 
       LIMIT 5;

            title            |     vowelless
-----------------------------+--------------------
 The Shining                 | Th Shnng
 Dune                        | Dn
 2001: A Space Odyssey       | 2001:  Spc dyssy
 The Cat in the Hat          | Th Ct n th Ht
 Bartholomew and the Oobleck | Brthlmw nd th blck
(5 rows)

trim()

trim(
side
 
f
 FROM 
s
 )

The trim() function is the SQL92 function used to achieve the same effects as PostgreSQL's rtrim(), ltrim(), and btrim() functions. It accepts three arguments, including a leading keyword side (which may be either LEADING, TRAILING, or BOTH), and two character strings, f and s .

When specified as LEADING, trim() behaves as ltrim(), trimming the longest substring from the beginning of the string s which consists solely of characters contained within f .

When specified as TRAILING, trim() behaves as rtrim(), trimming the longest substring from the end of the string s which consists solely of characters contained within f .

When specified as BOTH, trim() behaves as btrim(), trimming the longest substrings from both the beginning and end of the string s which consists solely of characters contained within f .

Examples

booktown=# 
SELECT isbn, trim(LEADING '0' FROM isbn)

booktown-# 
       FROM editions

booktown-# 
       LIMIT 2;

    isbn    |   ltrim
------------+-----------
 039480001X | 39480001X
 0451160916 | 451160916
(2 rows)

booktown=# 
SELECT isbn, trim(TRAILING 'X' FROM isbn)

booktown-# 
       FROM editions

booktown-# 
       LIMIT 2;

    isbn    |   rtrim
------------+------------
 039480001X | 039480001
 0451160916 | 0451160916
(2 rows)

booktown=# 
SELECT isbn, trim(BOTH '0X' FROM isbn)

booktown-# 
       FROM editions

booktown-# 
       LIMIT 2;

    isbn    |   btrim
------------+-----------
 039480001X | 39480001
 0451160916 | 451160916
(2 rows)

upper()

upper(
s
 )

The upper() SQL92 function accepts a single argument s of type text, and returns the character string with each character converted to lowercase as a value of type text.

Example

booktown=# 
SELECT title, upper(title)

booktown-# 
       FROM books

booktown-# 
       ORDER BY id ASC

booktown-# 
       LIMIT 3;

        title         |        upper
----------------------+----------------------
 The Tell-Tale Heart  | THE TELL-TALE HEART
 Little Women         | LITTLE WOMEN
 The Velveteen Rabbit | THE VELVETEEN RABBIT
(3 rows)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire