Types

TO_NUMBER

Converts text written the “human” way — with commas or a currency sign — into a number.

Syntax

TO_NUMBER(<text>, '<format>')

The format describes what goes where: 9 is a digit, , or G is the thousands separator, . or D is the decimal point, and other characters, such as $, are skipped. If the text is just a number like '42.5', you don't need TO_NUMBER: CAST or ::numeric is enough.

A comma between thousands

SELECT TO_NUMBER('12,345.67', '99,999.99') AS amount;

amount
12345.67

With a currency sign

SELECT TO_NUMBER('$1,250.00', '$9,999.99') AS price;

price
1250.00

Versus CAST

SELECT CAST('42.5' AS numeric) AS simple_cast,

TO_NUMBER('1,234.5', '9,999.9') AS with_format;

simple_cast	with_format
42.5	1234.5

CAST only copes with a “clean” number; 1,234.5 needs a format.

Watch out

CAST('12,345.67' AS numeric) throws invalid input syntax for type numeric — a value with commas needs TO_NUMBER.

The format has to fit all the digits, or the extra ones are silently dropped: TO_NUMBER('12345', '999') gives 123 without any error.

G and D depend on the server's locale settings: in some places the thousands separator is a space or a period. For predictable results, write , and . directly in the format.

Related functions

CAST TO_CHAR TO_DATE