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”