The TO_NUMBER function interprets a character string based on the number format given as an argument, converts it to a NUMERIC type value, and returns it. If the number format is not specified, returns all significant figures that are included in the character string as NUMERIC type numbers by default.
TO_NUMBER(string_argument[, format_argument ])
string_argument :
• character strings
• NULL
format_argument :
• character strings
• NULL
--selecting a number casted from a string in the specified format
SELECT TO_NUMBER('-1234');
to_number('-1234')
============================================
-1234
SELECT TO_NUMBER('12345','999999');
to_number('12345', '999999')
============================================
12345
SELECT TO_NUMBER('$12,345.67','C99,999.999');
to_number('$12,345.67', 'C99,999.999')
======================
12345.670
SELECT TO_NUMBER('12345.67','99999.999');
to_number('12345.67', '99999.999')
============================================
12345.670