Convert varchar2 column to number in Oracle

Question

I have a table emp_details.

The column emp_salary is of type varchar2 in which certain rows are null:

emp_salary
100,34
null
20,2
30,3
null

I need a result in which the column type is number:

emp_salary
100.34
null
20.2
30.3
null

My attempt in code is:

select emp_salary 
from emp_details
where emp_salary = to_number(emp_salary ,'9999D99','nls_numeric_characters=,.');

Error:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

How can I fix it?

I don't mean to compare the values. I just want to change the varchar to number. I will use it in a small part of my bigger function, using TO_NUMBER.

Answer 1

You are sort of almost there. You sound like you're doing a conversion of a selected value rather than a filter and you need to cater for NULLs so:-

SELECT TO_NUMBER(NVL(emp_salary,'0'),'9999D99','nls_numeric_characters=,.')
FROM emp_details