Back to list PostgreSQL How to Convert a String to a Numeric Value in PostgreSQL Database: PostgreSQL Operators: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: 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: 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: SELECT TO_NUMBER(' 5 800,79- ', 'FM9G999D99S' ); Here is the result: numeric -5800.79 The format string describes the string containing the number (input value as string). In this example, this mask contains the symbol ‘FM’, which removes leading and trailing spaces. The ‘9’ indicates one digit (in our example, 5) and ‘G’ represents a group of digits (in our example, one space indicates a group of thousands). Next, ‘999’ indicates three more digits (800). The ‘D’ symbol specifies a decimal marker (here, a point/dot ‘.’). After the decimal symbol comes ‘99’, or 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 this mask: symboldescription FMleading zeroes and padding blanks 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. Recommended courses: SQL Basics in SQL Server SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP See also: How to Trim Strings in SQL How to Remove Trailing Zeros from a Decimal in PostgreSQL How to Capitalize the First Letter of Every Word in PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.