TO_NUMBER

Syntax

TO_NUMBER
(
  value  IN { text | smallint | int | bigint | double precision | numeric }
)
RETURNS numeric;

TO_NUMBER
(
  value  IN numeric,
  fmt    IN numeric
)
RETURNS numeric;

Overview

The TO_NUMBER function converts the entered string or numeric value to an Oracle-style numeric value.

If entered as text, it converts the numeric format string to a number after adjusting the decimal and thousands separator characters based on your locale settings.

Also, type values of smallint, int, bigint anddouble precision are converted to numeric

types through internal logic specific to each type.

Input of type numeric is returned as is, and if a format model (fmt) is provided, additional conversion formats can be applied using the PG built-in function (pg_catalog.to_number).

Parameter

Parameter	Description
value	text, smallint, int, bigint, double precision, numeric type; Target value to convert.
fmt	numeric type; Format model that specifies the number format to be applied to text input. (ex. '99999.99') For supported format models, refer to the PG official documentation. Number format models

Example

-- Convert text to numeric (default conversion)SELECT oracle.TO_NUMBER('12345.67');

 to_number 
-----------
  12345.67
(1 row)

-- Convert numeric values using numeric values and numeric value format models
SELECT oracle.TO_NUMBER(12345.12467, 99999.9);

 to_number 
-----------
   12345.1
(1 row)

-- Convert integer values to numeric
SELECT oracle.TO_NUMBER(12345::int);

 to_number 
-----------
     12345
(1 row)


-- Convert double precision values to numeric
SELECT oracle.TO_NUMBER(12345.67::double precision);

 to_number 
-----------
  12345.67
(1 row)