2

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.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.