PostgreSQL to_number() Function

The PostgreSQL to_number() function converts the specified string to a number according to the given format.

to_number() Syntax

This is the syntax of the PostgreSQL to_number() function:

to_number(str TEXT, format TEXT) -> NUMERIC

Parameters

str

Required. A string that will to be converted to a number.

format

Required. The format used for converting this string. You can use the template patterns in the following table to construct numeric format strings.

Pattern	Description
9	digit position (can be discarded if it doesn’t matter)
0	digit position (will not be removed, even if it doesn’t matter)
.	decimal point
,	group (thousands) separator
PR	negative values in angle brackets
S	currency symbol (uses locale)
L	Currency symbol (uses locale)
D	decimal point (uses locale)
G	group separator (uses locale)
MI	minus sign in specified position (if number < 0)
PL	plus sign in specified position (if number > 0)
SG	plus/minus sign in specified position
RN	Roman numerals (between 1 and 3999)
TH,th	ordinal number suffix
V	shift specified number of digits
EEEE	exponent for scientific notation

Return value

The PostgreSQL to_number() function returns a numeric value converted from the specified string according to the specified format.

to_number() Examples

This example shows how to use the PostgreSQL to_number() function to convert a specified string to a numeric value according to the specified format.

SELECT to_number('123,456,789.012-', '999G999G999D999S');

   to_number
----------------
 -123456789.012