The PostgreSQL TO_NUMBER() function converts a character string to a numeric value.
Syntax
The syntax of PostgreSQL TO_NUMBER() function is as follows:
1 | 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.
1 2 3 4 5 | SELECT TO_NUMBER( '12,345.6-', '99G999D9S' ); |
The output is:
1 2 3 4 | 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:
1 2 3 4 5 | SELECT TO_NUMBER( '$1,234,567.89', 'L9G999g999.99' ); |
Here is the result:
1 2 3 4 | 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:
1 2 3 4 5 | SELECT TO_NUMBER( '1,234,567.89', '9G999g999' ); |
It returned 1234567 instead of 1234567.89 as follows:
1 2 3 4 | 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.