PostgreSQL to_number function.

Preamble

The PostgreSQL to_number function converts a string into a number.

Syntax of to_number function in PostgreSQL

to_number( string1, format_mask )

Parameters and function arguments

• string1 – A string that will be converted to a number.
• format_mask – The format that will be used to convert string1 to a number. This can be one of the following and can be used in many combinations.

The to_number function can be used in the following PostgreSQL versions

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Let’s look at some examples of to_number functions to see how to_number can be used in PostgreSQL.
For example:

SELECT to_number('1234.73', '9999.99');

--Result: 1234.73

SELECT to_number('1,234.73', '9G999.99');

--Result: 1234.73

SELECT to_number('1,234.73', 'L9G999.99');

--Result: 1234.73

SELECT to_number('$1,234.73', 'L9G999'); - Result: 1234.73;

--Result: 1234

PostgreSQL tutorial; functions

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.