The PostgreSQL TO_NUMBER() function converts a character string to a numeric value.
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'
);Code language: JavaScript (javascript)The output is:
to_number
-----------
-12345.6
(1 row)Code language: CSS (css)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'
);Code language: JavaScript (javascript)Here is the result:
to_number
-----------
1234567.89
(1 row)Code language: CSS (css)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'
);Code language: JavaScript (javascript)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.
