PostgreSQL TO_NUMBER() function – Complete tutorial

In this PostgreSQL tutorial, we will discuss the PostgreSQL TO_NUMBER() function to convert a string into the numeric type value based on some specified format, and will also cover the following topics:

• PostgreSQL TO_NUMBER function
• PostgreSQL TO_NUMBER format
• PostgreSQL TO_NUMBER format with leading zeros
• PostgreSQL TO_NUMBER format signed integers
• PostgreSQL TO_NUMBER format thousand separator
• PostgreSQL TO_NUMBER format with commas
• PostgreSQL TO_NUMBER format decimal places
• PostgreSQL TO_NUMBER from string
• PostgreSQL TO_NUMBER format currency
• PostgreSQL TO_NUMBER format as percentage
• PostgreSQL TO_NUMBER VS CAST

Are you new to PostgreSQL? Check out PostgreSQL installation on Linux step by step and How to create a table in PostgreSQL [Terminal + pgAdmin].

PostgreSQL TO_NUMBER function

PostgreSQL provides a useful and powerful set of tools to convert various data type values (integer, numeric, floating-point, timestamp/date-time) into formatted strings and vice-versa. These are the data type formatting functions.

The TO_NUMBER() is one of these data type formatting functions which is used to convert a string type value into a numeric type value with given specified formatting. The syntax is as follows:

TO_NUMBER(input_string, format)

In the above syntax,

• The input_string is the argument that represents the string (TEXT type) with formatted numeric value which is to be converted into the numeric type value by specifying the pattern of the input_string in the format, specified as the second argument.
• The format is the argument that specifies the pattern/format string (TEXT type) in the input_string argument of the function.
• The TO_NUMBER() function returns the numeric type value.

PostgreSQL TO_NUMBER format

The numeric value formatting patterns available in PostgreSQL, that can be used as the format string argument in the TO_NUMBER() function:

format	Description
9	Digit position, and is used to get the numeric value with a specified number of digits (can be removed if insignificant)
0	Digit position, and is used to get the numeric value with leading zeros (will not be removed, even if insignificant)
. (period)	Decimal point
, (comma)	Group separator (thousands separator for currency, etc)
FM	Suppresses the blank padding and leading zeroes (Fill Mode)
PR	A negative value in angle brackets
S	Sign anchored to a number (that uses locale)
L	Currency symbol (that uses locale)
D	Decimal point (that uses locale)
G	Group separator (that uses locale)
MI	Minus sign in specified position for the numbers less than 0
PL	Plus sign in specified position for the numbers greater than 0
SG	Plus/Minus sign in the specified position
RN	Roman numeral for the number that ranges from 1 to 3999
V	Shift specified number of digits (It multiplies the value by 10 raised to the power n, where n = number of digits following V).
TH / th	TH for Upper case and the for lower case ordinal number suffix
EEE	Exponent for the scientific notation

Now, you have covered all the format string patterns available in PostgreSQL for TO_NUMBER() function. So, let’s do some examples to make the concepts more clear.

Read PostgreSQL CASE with Examples

PostgreSQL TO_NUMBER format with leading zeros

You can convert a formatted string consisting numeric value with leading zeros into the numeric type value in the PostgreSQL by using the TO_NUMBER() function and specifying the leading zeros as 0 in the format string argument.

You can specify only one 0 at the start of the format string following either 0 or 9 to specify the digits in the input string.

The examples shown below will let you understand the usage properly.

SELECT TO_NUMBER('0071.0', '9999.9');

SELECT TO_NUMBER('0071.0', '9099.9');

SELECT TO_NUMBER('0071.0', '0999.9') AS with_leading_zeroes;

SELECT TO_NUMBER('000721.3', '000999.9');

SELECT TO_NUMBER('0071.0', '99.9');

PostgreSQL TO_NUMBER format signed integers

You can convert a signed integers/numeric value consisting strings into the numeric type in PostgreSQL by specifying the SI or MI (for minus sign)/PL (for plus sign) in the format string argument either at the beginning or at the end, according to the input string argument of the TO_NUMBER() function.

Example :

SELECT TO_NUMBER('+ 2,786.43', 'SI9G999D99') AS sign_at_the_begining,
TO_NUMBER('+2,786.43', 'PLFM9G999D99') AS with_padding_using_PL,
TO_NUMBER('2,786.43+', 'FM9G999D99PL') AS sign_at_the_back;

SELECT TO_NUMBER('- 2,786.43', 'SI9G999D99') AS sign_at_the_begining,
TO_NUMBER('-2,786.43', 'MIFM9G999D99') AS with_padding_using_MI,
TO_NUMBER('2,786.43-', 'FM9G999D99MI') AS sign_at_the_back;

Read PostgreSQL WHERE with examples

PostgreSQL TO_NUMBER format thousand separator

You can convert the string input consisting a numeric value with thousand separator formatting, into the numeric type value in PostgreSQL by specifying the separator at the corresponding places in the format string argument of the TO_NUMBER() function.

The separator in the PostgreSQL that you can use is either a group separator (G) or a comma separator (,).

We will be using the group separator (G) in the below examples:

SELECT TO_NUMBER('6128.700', '9999.999') AS without_separator,
TO_NUMBER('6,128.700', '9G999.999') AS with_group_separator;

SELECT TO_NUMBER('0028.6', '0999.9') AS without_separator,
TO_NUMBER('0,028.6', '0G099.9') AS with_group_separator;

SELECT TO_NUMBER('-2678000', 'SG9999999') AS without_separator,
TO_NUMBER('-2,678,000', 'SG9G999G999') AS with_group_separator;

PostgreSQL TO_NUMBER format with commas

You can convert the string input consisting of a numeric value with separator formatting, into the numeric type value in PostgreSQL by specifying the separator at the corresponding places in the format string argument of the TO_NUMBER() function.

The separator in the PostgreSQL that you can use is either a group separator (G) or a comma separator (,).

We will be using the comma (,) separator in the below examples:

SELECT TO_NUMBER('6,128.700', '9999.999') AS without_separator,
TO_NUMBER('6,128.700', '9,999.999') with_comma_separator;

SELECT TO_NUMBER('0028.6', '0999.9') AS without_separator,
TO_NUMBER('0,028.6', '0,999.9') with_comma_separator;

SELECT TO_NUMBER('-2678000', 'MI9999999') AS without_separator,
TO_NUMBER('-2,678,000', 'MI9,999,999') AS with_group_separator;

Read PostgreSQL drop all tables

PostgreSQL TO_NUMBER format decimal places

You can convert and manipulate decimal places of a string input consisting of formatted numeric data into the numeric type value in PostgreSQL by using the format strings, D (for specifying the decimal place in the input string), EEE (for converting exponent form to the numeric form), V (for shifting the decimal place to left or right) in the format string argument.

Example :

SELECT TO_NUMBER('6,128.700', '9G999.999') AS using_dot,
TO_NUMBER('6,128.700', '9G999D999') AS using_D;

SELECT TO_NUMBER('0.0042', '0.9999') AS using_dot,
TO_NUMBER('0.0042', '0D9999') AS using_D;

SELECT TO_NUMBER('6,128.700', '9G999.999') AS using_dot,
TO_NUMBER('6,128.700', '9G999D999') AS using_D;

SELECT TO_NUMBER('7.87e+06', '9.99EEE') AS scientific_notation;

SELECT TO_NUMBER('274.68', '9V9999') AS decimal_shift_to_left,
TO_NUMBER('274.68', '999V99') AS decimal_shift_to_right;

Read PostgreSQL DROP COLUMN + 7 examples

PostgreSQL TO_NUMBER from string

You can convert input strings consisting of strings and numeric values, into numeric type values, by neglecting the strings in the PostgreSQL by specifying the X in place of the string characters in the format string argument of the TO_NUMBER() function.

Example :

SELECT TO_NUMBER('I have 21 apples, I can lend 15 of them', '"XXXXXX" 99 "XXXXXXXXXXXXXXXXXX" 99 "XXXXXXX");

SELECT TO_NUMBER('C.Ronaldo is 36 years old', '"XXXXXXXXXXXX" 99 "XXXXXXXXX"');

PostgreSQL TO_NUMBER format currency

You can convert a formatted input string consisting a currency symbol and numeric value in the PostgreSQL by specifying the L either at the beginning or at the end in the format string argument, corresponding to the input string of the TO_NUMBER() function.

Example :

SELECT TO_NUMBER('$ 2,786.43', 'L9G999D99') AS from_currency_symbol_at_the_begining,
TO_NUMBER('$2,786.43', 'LFM9G999D99') with_padding,
TO_NUMBER('2,786.43$', 'FM9G999D99L') AS currency_symbol_at_the_end;

Read PostgreSQL INSERT Multiple Rows

PostgreSQL TO_NUMBER format as percentage

You can convert a formatted input string consisting of the percentage sign with the numeric value in the PostgreSQL by specifying the % in the format string argument corresponding to the position in the input string of the TO_NUMBER() function.

Example :

SELECT TO_NUMBER('We got 48.33%', '"XXXXXX" 99D99%') AS from_percentage;

PostgreSQL TO_NUMBER VS CAST

As we have discussed above, the TO_NUMBER() function in PostgreSQL is used to convert a formatted string to the numeric type. You can say that, you have to specify the format explicitly to convert a formatted string to a numeric type.

And, there is a function CAST() in PostgreSQL, that is used to convert a value of one type into another, that is you can convert a string type value to an integer type using the CAST() function.

It converts a string type into an integer type implicitly by its own, you just have to specify the type to which it has to be converted as the argument of the function. You cannot convert a formatted string into an integer value using the CAST() function.

The syntax is as follows:

CAST(expression AS cast_type)

In the above syntax,

• The expression can be an expression resulting a value, a column name of a table or can be a constant value.
• The cast_type defines the type to which the value of the expression has to be converted.

Let’s do some examples to clarify the mentioned concepts.

SELECT '248.7' AS input_string, TO_NUMBER('284.7', '999.9');
SELECT CAST('284.7' AS INTEGER);

SELECT '248.7' AS input_string, TO_NUMBER('284.7', '999.9'),
CAST('284.7' AS DOUBLE PRECISION);

SELECT '  248.7  ' AS input_string, TO_NUMBER('  284.7  ', 'FM999.9'),
CAST('  284.7  ' AS DOUBLE PRECISION);

SELECT '248.7%' AS input_string, TO_NUMBER('284.7%', '999.9%');
SELECT CAST('284.7%' AS DOUBLE PRECISION);

SELECT '$248.7' AS input_string, TO_NUMBER('$284.7', '$999.9');
SELECT CAST('$284.7' AS DOUBLE PRECISION);

SELECT '-248.7' AS input_string, TO_NUMBER('-284.7', 'SI999.9'),
CAST('-284.7' AS DOUBLE PRECISION);

SELECT '248.7-' AS input_string, TO_NUMBER('284.7-', '999.9MI');
SELECT CAST('284.7-' AS DOUBLE PRECISION);

SELECT ' Hello 007' AS input_string,
TO_NUMBER('Hello 007', '"XXXXX" 099');
SELECT CAST('Hello 007' AS INTEGER);

You may like the following PostgreSQL tutorials:

• PostgreSQL TO_TIMESTAMP function
• Postgresql change column data type
• PostgreSQL TO_CHAR function
• How to import CSV file into PostgreSQL
• PostgreSQL Export Table to CSV
• Postgresql date_trunc function

In this PostgreSQL tutorial, we have learned about PostgreSQL TO_TIMESTAMP() function to convert a string into the numeric type value based on some specified format, and have also covered the following topics:

• PostgreSQL TO_NUMBER function
• PostgreSQL TO_NUMBER format
• PostgreSQL TO_NUMBER format thousand separator
• PostgreSQL TO_NUMBER format with commas
• PostgreSQL TO_NUMBER format decimal places
• PostgreSQL TO_NUMBER format currency
• PostgreSQL TO_NUMBER format with leading zeros
• PostgreSQL TO_NUMBER format as percentage
• PostgreSQL TO_NUMBER(text text)
• PostgreSQL TO_NUMBER hex
• PostgreSQL TO_NUMBER hex string
• PostgreSQL TO_NUMBER to words
• PostgreSQL TO_NUMBER VS CAST