TO_NUMBER ​

Return a numeric value from a character string, based on a specified format.

TO_NUMBER(string, format)

string

The input string is the first argument, and its format is the second argument. The first argument must be a literal character string, an expression that evaluates to a character string, or a value that is implicitly cast to a character string.

Space characters are allowed only at the beginning of the input string, before any data character.

The input string must be shorter than or equal to the format string. For example, the following query returns an expected error because the input string has three digits but the format string has only two:

yellowbrick=# select to_number('123','99') from sys.const;
ERROR:  Invalid input number for TO_NUMBER

format

You must specify the format of the input string, using a supported list of elements. See Formats for Numeric Values for formatting rules and examples. The format cannot be an empty string ('').

The DECIMAL return type of the function matches the precision and scale of the format string. You cannot use the TO_NUMBER function to return a value with more than 38 digits. See DECIMAL for details about precision and scale rules for Yellowbrick DECIMAL values.

Note: This function is implemented for Oracle compatibility, not PostgreSQL compatibility.

Examples ​

The following TO_NUMBER examples demonstrate the behavior of the elements listed in Formats for Numeric Values.

Return a number that includes a decimal point:

premdb=# select to_number('123.45','999D99') from sys.const;
 to_number 
-----------
   123.45
(1 row)

premdb=# select to_number('123.45','999.99') from sys.const;
 to_number 
-----------
   123.45
(1 row)

Return a number that has a thousands separator and a decimal point:

premdb=# select to_number('1,123.45','9G999D99') from sys.const;
 to_number 
-----------
   1123.45
(1 row)

premdb=# select to_number('1,123.45','9,999D99') from sys.const;
 to_number 
-----------
   1123.45
(1 row)

Return a negative number that has angle brackets:

premdb=# select to_number('<1,123.45>','9G999D99PR') from sys.const;
 to_number 
-----------
  -1123.45
(1 row)

Return a negative number that has a minus sign:

premdb=# select to_number('-1,123.45','S9G999D99') from sys.const;
 to_number 
-----------
  -1123.45
(1 row)

Return a negative number that ends with a minus sign:

premdb=# select to_number('123-','999MI') from sys.const;
 to_number 
-----------
     -123
(1 row)

Use a combination of a 0 and two 9 digits:

premdb=# select to_number('12.34', '990.99') from sys.const;
 to_number 
-----------
    12.34
(1 row)

The following example shows the basic difference between 9 and 0 values:

premdb=# select to_number('12.34','9999D9999');
 to_number 
-----------
   12.3400
(1 row)

premdb=# select to_number('12.34','0000D0000');
ERROR:  Invalid input number for TO_NUMBER

Attempt to use leading 0 values for numbers where an exact number of digits is required:

premdb=# select to_number('12.34', '099.99') from sys.const;
ERROR:  Invalid input number for TO_NUMBER
premdb=# select to_number('12.34', '0.99') from sys.const;
ERROR:  Invalid input number for TO_NUMBER

Attempt to convert a string that contains the $ sign:

premdb=# select to_number('$12.34', '99.99') from sys.const;
ERROR:  Invalid input number for TO_NUMBER

The following example uses an expression on a character column (ftscore) in the first argument to the TO_NUMBER function:

premdb=# select ftscore, to_number(substr(ftscore,1,1), '9') from match order by 1 desc limit 3;
 ftscore | to_number 
---------+-----------
 9-1     |         9
 9-0     |         9
 8-2     |         8
(3 rows)

premdb=# select ftscore, to_number(substr(ftscore,1,1), '9D9') from match order by 1 desc limit 3;
 ftscore | to_number 
---------+-----------
 9-1     |       9.0
 9-0     |       9.0
 8-2     |       8.0
(3 rows)

The following example shows that the first argument may be implicitly cast to a string, then returned as a number:

premdb=# select seasonid, to_number(seasonid*10, '99999') from season order by seasonid desc limit 3;
 seasonid | to_number 
----------+-----------
      25 |       250
      24 |       240
      23 |       230
(3 rows)

Note that the expression seasonid*10, where seasonid is an integer column, does not require an explicit cast.

Parent topic:Formatting Functions