Convert string to number in oracle using TO_NUMBER function

By Santhosh N

Use Oracle TO_NUMBER to convert a string to a number and format the number in the desired nls language.

Oracle Syntax: TO_NUMBER(str, [Format], [NlsLang])

Here str is the string that needs to be converted, and Format is the desired output format that needs to be converted to, and NlsLang is the language used to convert the str to number format. Here, last two parameters are optional.

Example 1: SELECT TO_NUMBER('123.05','999.99') FROM dual;
This returns “123.05” as result

Example 2: SELECT TO_NUMBER('123.00','999.99') FROM dual;
This returns “123” as result

Example : SELECT TO_NUMBER('123.45') FROM dual;
This returns “123.45” as result

Related FAQs

What is the real purpose of dual in oracle.
This explains how to convert the string to date format in oracle.
This explains how to replace the part(s) of the string with other string or char
This explains how to convert all the characters to the lower case alphabets
This explains how to convert the given number to the interval (which can be days, hours, minutes, or seconds) in the oracle using the NUMTODSINTERVAL conversion function
This explains how to convert one character set strings to other character set strings in oracle. Also, used to convert one data type to other in oracle
Convert string to number in oracle using TO_NUMBER function  (14598 Views)