TO_NUMBER Function in Oracle SQL – PLSQL

The TO_NUMBER function in Oracle SQL / PLSQL is used to convert a string to a number data type

The Syntax for the TO_NUMBER function in Oracle SQL / PLSQL is:

SELECT TO_NUMBER(string1, [FORMAT_MASK], [NLS_LANGUAGE])
FROM table_name;

  • string1 is the string that has to be converted to number
  • FORMAT_MASK is optional; it is used to convert the string1 to number.
  • NLS_LANGUAGE is also optional and is used to convert the string1 to number.

Example 1:

SELECT TO_NUMBER('123.456',999.999)
FROM DUAL; 

Will return “123.456”


Example 2:

SELECT TO_NUMBER('123.4',999.9)
FROM DUAL;

Will return “123.4”


Example 3:

SELECT TO_NUMBER('123',999.9)
FROM DUAL;

Will return “123”


Tagged , , , , , , , , , , , . Bookmark the permalink.