PostgreSQL To_Number

The TO_NUMBER() Function stands out as a handy tool when working with string representations of numeric values. In this PostgreSQL tutorial, I will teach you how to use the PostgreSQL To_Number function with multiple examples.

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.

Syntax

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.

Format Patterns

The format chart is provided below, which you can use for converting a string 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

Example 1

Let’s take an example and understand how to use the PostgreSQL to_number() function.

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');

After executing the above query, I obtained the expected output, as shown in the screenshot below.

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 that there are three digits within a string (12.1), the two-digit number 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 the digit value is 9.

Here, each format digit or value represents a single digit within the string value. If you forget to specify the format for the string, you will receive an error, and the to_number() function cannot determine which format to use for converting the string value.

Now, use the same example, but 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 picture below 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 digits nine before or after the decimal to convert it into a numeric value.

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

Example 2: 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 it to a numeric amount, use the command below.

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

After executing the above query, I obtained the expected output, as shown in the screenshot below.

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.

Example 3: For Thousand Separator

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

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

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

After executing the above query, I got the expected output as per the screenshot below.

In the above query within the format ‘L999,999’, to separate the digits 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 code below.

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

Example 4: For Leading and Trailing Spaces

Sometimes the string value that you want to convert to a numeric value using the to_number() function contains 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 a string value ‘ 345.55 ‘ that contains spaces. Use the command below to remove the spaces.

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

After executing the above query, I obtained the expected output, as shown in the screenshot below.

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 a numeric value.

Performance Considerations

When working with large datasets. Here are some tips I’ve learned

1. Indexing: Avoid using TO_NUMBER in WHERE clauses on indexed columns
2. Materialized Views: Pre-convert values for reporting tables
3. Data Type Selection: Store as numeric from the beginning when possible
4. Batch Processing: For large data conversions, process in batches

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, such as currency, group separator, and fill mode, that are 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