PostgreSQL: to_number Function
This PostgreSQL tutorial 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)
Advertisements