PostgreSQL Tutorial: TO_NUMBER Function

September 19, 2023

Summary: The PostgreSQL TO_NUMBER() function converts a character string to a numeric value.

Table of Contents

Syntax

The syntax of PostgreSQL TO_NUMBER() function is as follows:

TO_NUMBER(string, format)

The TO_NUMBER() function requires two arguments.

String to be converted to a number. Its format must be a literal value.

The format argument indicates how the first argument should be interpreted to create the number.

The following table illustrates the list of valid formats:

Format	Description
9	Numeric value with the specified number of digits
0	Numeric value with leading zeros
. (period)	decimal point
D	decimal point that uses locale
, (comma)	group (thousand) separator
FM	Fill mode, which suppresses padding blanks and leading zeroes.
PR	Negative value in angle brackets.
S	Sign anchored to a number that uses locale
L	Currency symbol that uses locale
G	Group separator that uses locale
MI	Minus sign in the specified position for numbers that are less than 0.
PL	Plus sign in the specified position for numbers that are greater than 0.
SG	Plus / minus sign in the specified position
RN	Roman numeral that ranges from 1 to 3999
TH or th	Upper case or lower case ordinal number suffix

Noted that these format strings also apply to TO_CHAR() function.

The TO_NUMBER() function returns a value whose data type is numeric.

Let’s take a look at some examples of using the TO_NUMBER() function to understand how it works.

The following statement converts the string ‘12,345.6-‘ to a number.

SELECT
    TO_NUMBER(
        '12,345.6-',
        '99G999D9S'
    );

The output is:

 to_number
-----------
  -12345.6
(1 row)

The following example shows how to convert a money amount to a number:

SELECT
    TO_NUMBER(
        '$1,234,567.89',
        'L9G999g999.99'
    );

Here is the result:

 to_number
-----------
 1234567.89
(1 row)

If you don’t specify .99 in the format string, the TO_NUMBER() function will not parse the part after the decimal place.

See the following example:

SELECT
    TO_NUMBER(
        '1,234,567.89',
        '9G999g999'
    );

It returned 1234567 instead of 1234567.89 as follows:

 to_number
-----------
   1234567
(1 row)

In this tutorial, you have learned how to use the PostgreSQL TO_NUMBER() function to convert a string to a numeric value.

PostgreSQL Tutorial: String Functions