TO_NUMBER

Oracle

TO_NUMBER(char_value, [format_mask], [nls_language])

• char_value: It is the character string that will be converted to a number. This character string typically represents a numeric value in a particular format.
• format_mask: This optional parameter specifies the format that will be used to interpret char_value. It consists of a series of characters which represent a date format. If this parameter is not provided, the char_value is interpreted using the default number format.
• nls_language: Another optional parameter, which specifies the language in which the number is represented. If this parameter is not provided, the default language for the session is used. This can change the way certain elements are interpreted, like decimal separators or group separators.

Example

SELECT TO_NUMBER('1000.10', '9999.99') as TO_NUMBER_Example
FROM dual;

Output

TO_NUMBER_Example
-----------------
          1000.1

Explanation

The TO_NUMBER function in Oracle converts a text or expression into a number. The first parameter of TO_NUMBER is the expression to convert, and the second parameter is a format model that defines the format of the number. In this example, the string '1000.10' is being converted to the number 1000.1 using the pattern '9999.99'.

PostgreSQL

TO_NUMBER(text, format)

• text: This parameter is the string that will be converted to a number. It represents the data that you want to convert. The string can be of a format that consists of digits, a sign that could either be ’+’ or ’-’, a decimal point, or a group separator such as a comma or period.
• format: This parameter is a string that specifies the format that the ‘text’ string is in. It is used to interpret the number in the text. It can be a combination of digits, decimal points, group separators, plus and minus signs.

Example

SELECT TO_NUMBER('1000', '9999');

Output

1000

Explanation

The TO_NUMBER function converts a formatted text string to a numeric value in PostgreSQL. In this example, ‘1000’ is the string to be converted and ‘9999’ is the format model. The output reflects the result of the conversion, returning a numeric value of 1000.