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:
[sourcecode language=”sql”]
SELECT TO_NUMBER(‘123.456’,999.999)
FROM DUAL; [/sourcecode]

Will return “123.456”


Example 2:

[sourcecode language=”sql”]
SELECT TO_NUMBER(‘123.4’,999.9)
FROM DUAL;[/sourcecode]

Will return “123.4”


Example 3:
[sourcecode language=”sql”]
SELECT TO_NUMBER(‘123’,999.9)
FROM DUAL;[/sourcecode]

Will return “123”


Leave a Reply

Your email address will not be published. Required fields are marked *