Error on query with "Replace"

Question

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

beiller · Answer 1 · 2014-07-18T15:20:30.563

0

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 ;

edited Jul 18 '14 at 15:20

answered Jul 18 '14 at 12:47

beiller

3,105
1
11
19

But the view "TestVue" check if Row2 is not null. Anyway, I have used your query to check if the row is empty, and I have no empty column. I edit my post principal with your question – Zoners Jul 18 '14 at 12:52
Yes but blank and NULL are different. See if you can find the bad row using a query like @Gordon Linoff 's – beiller Jul 18 '14 at 12:54
This will return nothing. [NULL and the empty string are identical in Oracle](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null) and you can't compare NULLs with an equality. – Ben Jul 18 '14 at 12:59
Okay but are you down voting though because this answers the question. Yes your link shows me NULL and '' are equivalent in oracle. Doesn't make sense to me but is also neither here nor there. – beiller Jul 18 '14 at 15:18
Updated to use IS NULL :) Just confirmed "= '' " wont return blank rows if its VARCHAR2 only. Silly oracle. – beiller Jul 18 '14 at 15:21

Gordon Linoff · Answer 2 · 2014-07-21T11:18:00.430

0

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.

edited Jul 21 '14 at 11:18

answered Jul 18 '14 at 12:53

Gordon Linoff

1,242,037
58
646
786

I have this error when I use your query: ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator" *Cause: *Action: Error at Line: 1 Column: 110 – Zoners Jul 21 '14 at 07:03
@Zoners . . . That has been fixed. (There needed to be a comparison after all those string operations ;) – Gordon Linoff Jul 21 '14 at 11:18
I have no error, but the query return no result. If I have the value "46.07" in "Row2" , and I try your query with ">0" and not "=0" I have no result. Thanks for your help – Zoners Jul 21 '14 at 11:50
@Zoners . . . Hmmm. Perhaps you have two decimal points. You can look for `value like '%.%.%'`. – Gordon Linoff Jul 21 '14 at 13:57

Tarciso Junior · Answer 3 · 2014-07-18T16:08:54.847

0

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 ;

edited Jul 18 '14 at 16:08

answered Jul 18 '14 at 12:56

Tarciso Junior

549
9
16

Why do you think this will work better than the original? If you are just suggesting he try it to see if it works, that's not an answer. – itsbruce Jul 18 '14 at 13:22
this solve the invalid number error, considering that the field is not null. If not, may need to use a NVL funcion too: Select * from TestView where to_number(Replace(NVL(Row2,'0'),'.',',')) > 0 – Tarciso Junior Jul 18 '14 at 14:55

Error on query with "Replace"

3 Answers3