How to use PostgreSQL To_Number

In this PostgreSQL tutorial, I will teach you how to use the PostgreSQL To_Number function.

You will learn through different examples how to convert the given string value to numeric using the to_number function. Also, learn about the different formats for converting the string value to numeric value.

How to use PostgreSQL To_Number

The to_number() function of PostgreSQL converts the given string into a number or numeric value. This function takes two values, the actual string and the format for converting that string.

The syntax is given below.

to_number(string_value, format_for_string)

• string_value: It is the actual string that you want to convert into a numeric value.
• format_for_string: It is used to specify the format in which the string is converted.

The format chart is given below that you can use while for string conversion to a numerical value.

String Format	Meaning
9	It represents the single digit without leading zeros.
.	Use when decimals exist in the string.
L	It represent the currency symbol
S	It denotes the sign.
,	This comma is a group separator.
G	It is a group separator.
0	It denotes the digit with leading zeros.
RN	It is a random value between 1 to 3999.
FM	It is called Fill mode to remove the leading and trailing spaces.
SG	It is used for plus or minus signs.
MI	It is a minus sign for a negative value (less than 0).
PL	This represents the plus sign for a positive value (greater than 0).
RN	It denotes the Roman Numerals between 1 to 3999.
D	It denotes the decimal point
TH or	It denotes the suffix of the ordinal number

First, supply the string value (‘12.1’) to the to_number() function with format (99.9) and see what happens.

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

The above query returns the output as a numeric value 12.1 which you can see, let’s understand this string and format thing.

First, see there is three-digit within a string (12.1), the two-digit before the decimal point and one after the decimal point. if you see the format it also has the same position (99.9) for the digits but here digit value is 9.

Here each format digit or value represents the single digit for the digit within the string value. if you forgot to specify the format for the string, you will get an error, and the to_number() function doesn’t know to which format to convert the string value.

Now use the same example but this time change the format from (99.9) to (9.9) and keep the same string value.

SELECT TO_NUMBER('12.1' , '9.9');

The above query shows the error ‘numeric field overflow’. This is because you haven’t used the correct format for the string value.

Look at the below picture to understand how to use the to_number() function with a different format.

From the above picture, you can understand that the number of digits within a string value is related to the number of format digit 9 before or after the decimal to convert it into a numeric value.

Here format one 9 represents the one digit within the string value. if you are using a string with 5 different digits then in the format provide the five 9 to convert this string value to numeric value.

How to use PostgreSQL To_Number for Currency

If your string value within the to_number() function contains a currency symbol or an amount, then you can also convert that amount to a numeric value. The format ‘L’ is used to denote the currency within the string value.

Suppose you have a string value of ‘$234.4’ and you want to convert this string value numeric amount, use the below command.

SELECT TO_NUMBER('$234.4','L999.9');

In the above output, the string amount ‘$234.4’ is converted to the numeric amount of 234.4. if you don’t specify the format ‘L’, then it doesn’t show the decimal part.

How to use PostgreSQL To_Number for Thousand Separator

If you have an amount as a string value that contains the separator like comma (,) at the thousand positions, then you can also convert that amount with the separator at the thousand positions.

Let’s see through an example, suppose you have an amount of ‘$130,000’ with a comma separator and want to convert this into a number amount, then use the below command.

SELECT TO_NUMBER('$130,000','L999,999');

In the above query within the format ‘L999,999’, to separate the digit as specified in the string value, the comma (,) is also used in the format at the same position as in the string value.

Instead of a comma (,) within the format, you can also use the format ‘G’ to represent the separator as shown in the below code.

SELECT TO_NUMBER('$130,000','L999G999');

How to use PostgreSQL To_Number Leading and Trailing Spaces

Sometimes the string value that you want to convert to a numeric value using the to_number() function contains the spaces. To remove the spaces from the string value and convert it to a numeric value, you can use the format ‘FM’ which stands for fill mode.

Now you have string value ‘ 345.55 ‘ and this contains spaces, use the below command to remove the spaces.

SELECT TO_NUMBER('  345.55  ','FM999.99');

In the above query, the string value ‘ 345.55 ‘ contains leading and trailing spaces. To remove these spaces, the ‘FM’ is used within the format ‘FM999.99’. As in the output you can see the numeric value without the leading and trailing spaces.

If you have different kinds of string values like (string contains plus or minus symbol) then you can refer to the format chart to convert it into numeric value.

Conclusion

In this PostgreSQL tutorial, you learned how to convert the specified string value within the to_number() function into a numeric value. You have learned about different formats like currency, group separator, and fill mode that is used in the to_number() function.

You may like to read the following articles:

• How to Define Auto Increment in PostgreSQL
• How to Replace Special Characters in Postgres