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)
Arguments
The TO_NUMBER()
function requires two arguments.
1) string
String to be converted to a number. Its format must be a literal value.
2) format
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.
Return Value
The TO_NUMBER()
function returns a value whose data type is numeric.
Examples
Let’s take a look at some examples of using the TO_NUMBER()
function to understand how it works.
A) Convert a string to a number
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)
B) Convert a money amount to a number
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)
C) Format control
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.
See more
PostgreSQL Tutorial: String Functions
PostgreSQL Documentation: Data Type Formatting Functions