PostgreSQL: to_number Function

In this PostgreSQL post explains how to use the PostgreSQL to_number function with syntax and examples.

Description

The PostgreSQL to_number function converts a string to a number.

Syntax

The syntax for the to_number function in PostgreSQL is:

to_number( string1, format_mask )

Parameters or Arguments

string1

The string that will be converted to a number.

format_mask

The format that will be used to convert string1 to a number. It can be one of the following and can be used in many combinations.

Parameter	Explanation
9	Value (with no leading zeros)
0	Value (with leading zeros)
.	Decimal
,	Group separator
PR	Negative value in angle brackets
S	Sign
L	Currency symbol
D	Decimal
G	Group separator
MI	Minus sign (for negative numbers)
PL	Plus sign (for positive numbers)
SG	Plus/minus sign (for positive and negative numbers)
RN	Roman numerals
TH	Ordinal number suffix
th	Ordinal number suffix
V	Shift digits
EEEE	Scientific notation

Applies To

The to_number function can be used in the following versions of PostgreSQL:

• PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4

Example

Let's look at some PostgreSQL to_number function examples and explore how to use the to_number function in PostgreSQL.

For example:

postgres=# SELECT to_number('1210.73', '9999.99');
 to_number
-----------

   1210.73
(1 row)

postgres=# SELECT to_number('1,210.73', '9G999.99');
 to_number
-----------

   1210.73
(1 row)

postgres=# SELECT to_number('$1,210.73', 'L9G999.99');
 to_number
-----------

   1210.73
(1 row)

postgres=# SELECT to_number('$1,210.73', 'L9G999');
 to_number
-----------

      1210
(1 row)