Convert varchar2 column to number in Oracle 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.
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