How to Convert a String to a Numeric Value in PostgreSQL

• TO_NUMBER()
• CAST
• ::

Problem:

You’d like to convert a string to a decimal value in PostgreSQL.

Let’s convert the value in a string to a `DECIMAL` datatype.

Solution 1: Using the :: operator

We’ll use the `::` operator. Here’s the query you’d write:

```SELECT ' 5800.79 '::DECIMAL;
```

Here is the result:

numeric
5800.79

As you notice, the leading and trailing spaces were removed.

Discussion:

Use the `::` operator to convert strings containing numeric values to the `DECIMAL` data type. In our example, we converted the string `' 5800.79 '` to `5800.79` (a `DECIMAL` value).

This operator is used to convert between different data types. It’s very popular within PostgreSQL. You can also use the standard SQL operator, `CAST()`, instead of the `::` operator.

Solution 2: Using the CAST() function

```SELECT CAST(' 5800.79 ' AS DECIMAL );
```

Here is the result:

numeric
5800.79

Notice that `CAST()`, like the `::` operator, removes additional spaces at the beginning and end of the string before converting it to a number.

The PostgreSQL database provides one more way to convert. Use the `TO_NUMBER()` function if you need to convert more complicated strings. This function takes two arguments: the string to convert and the format mask that indicates how each character in the string should be interpreted. See the example below:

Solution 3: Using TO_NUMBER() function

```SELECT TO_NUMBER(' 5 800,79- ', 'FM9G999D99S' );
```

Here is the result:

numeric
-5800.79

The function takes two arguments: an input string to be converted to a number value (in our example `' 5 800,79-'`) and the format string (in our example `'FM9G999D99S'`).The format string describes in what format the input string containing the number is given. As you can see, the input format can be quite bizarre and complicated.

In our example, this mask contains the symbol `FM`, which removes leading and trailing spaces. The `9` indicates one digit (in our example, 5), `G` represents a digit group separator (in our example, one space indicating a group of thousands). Next, `999` indicates three more digits (800). The `D` symbol specifies a decimal separator (here, a decimal point `.`). After the decimal symbol comes `99` representing two fractional digits. The last symbol, `S`, specifies the use of a plus or minus sign (our number is negative, so it gets a minus).

Here are the most used symbols for the format:

symboldescription
9one digit
.local decimal point
Ggroup separator
Dlocal decimal separator
Sminus or plus sign
Llocal currency symbol

You can find more numeric formatting information in the PostgreSQL documentation.