CODEWITHSUNDEEP
sqloracle-database
Zoners
Zoners

Reputation: 123

Error on query with "Replace"

I Have an error on a oracle server but I don't understand why is not work. I use the software oracle sql developer.

The query is:

SELECT * FROM TestView WHERE REPLACE(TestView.Row2, '.', ',') > 0 ;

The value who is in TestVue.Row2 : '46.08','-46.47','1084.05','66500',... "TestView" is a view who check to return a row without empty value

When I Execute the query I have always an error who says:

ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: *Action:

Thanks for your help

Zoners

Upvotes: 0

Views: 260

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You should be able to find the row with something like:

select Row2
from TestVuw
where length(trim(translate(Row2, '+-.,0123456789', ' '))) = 0

Of course, this allows multiple +, -, ., and ,. It can be further refined using a regular expression, but this usually works to find the bad ones.

Upvotes: 0

Tarciso Junior
Tarciso Junior

Reputation: 549

Use a NVL Function to avoid errors caused by null field and a TO_NUMBER function to cast into a number before comparision with zero

SELECT * FROM TestView WHERE TO_NUMBER(REPLACE(NVL(TestView.Row2,'0'), '.', ',')) > 0 ;

Upvotes: 0

beiller
beiller

Reputation: 3135

You have a row in your table, which cannot be parsed into number format. Most likely it is a blank column.

SELECT * FROM TestVue WHERE TestVue.Row2 IS NULL ;

Upvotes: 0

Related Questions