CODEWITHSUNDEEP
sqloracle-database
Imran Hemani
Imran Hemani

Reputation: 629

"invalid number" occurs even after where clause - Oracle

I have two records in the table: 

STORE   VARCHAR2_1
30816   26.399883
30074   29.58.20.9

  SELECT *
    FROM mig_test sce
   WHERE VARCHAR2_1 > 90 
   AND VARCHAR2_1 not LIKE '%.%.%';

I am querying: "invalid number". Even when I have excluded the faulty record in the where clause

Upvotes: 0

Views: 85

Answers (3)

VBoka
VBoka

Reputation: 9083

I would approach it with a sub query and with regexp_count to eliminate the data with more than one full stop '.':

select * 
from (SELECT REGEXP_COUNT(sce.VARCHAR2_1, '[.]') cnt
             , sce.store  
             , VARCHAR2_1
      FROM mig_test sce)
where cnt = 1
and CAST(VARCHAR2_1 as number) > 90;

Here is a demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

SQL is free to rearrange computations however it likes. So, that makes your comparison troublesome, because the the value may not be a number.

However, case does guarantee the order of evaluation of expressions, so you can try:

SELECT *
FROM mig_test sce
WHERE (CASE WHEN VARCHAR2_1 NOT LIKE '%.%.%'
            THEN CAST(VARCHAR2_1 as number)
       END) > 90

Note that I added in an explicit cast().

Alternatively, you can use regular expressions:

WHERE REGEXP_LIKE(VARCHAR2_1, '^(9|[1-9][0-9])[0-9]+[.]?[0-9]*$')

That is, it starts either with 9# or ### and is followed by digits and at most one decimal point.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142720

It is not the second condition that raises the error, but the first one:

SQL> SELECT *
  2      FROM mig_test sce
  3     WHERE VARCHAR2_1 > 90                 --> this
  4     AND VARCHAR2_1 not LIKE '%.%.%';
   WHERE VARCHAR2_1 > 90
         *
ERROR at line 3:
ORA-01722: invalid number

As the column is VARCHAR2, enclose 90 into single quotes:

SQL> SELECT *
  2      FROM mig_test sce
  3     WHERE VARCHAR2_1 > '90'
  4     AND VARCHAR2_1 not LIKE '%.%.%';

no rows selected

SQL>

See whether CAST helps. 

with valid_values as
  (select store, 
          cast (varchar2_1 as number) varc     --> cast to number
   from mig_test
   where regexp_count(varchar2_1, '\.') <= 1   --> remove invalid rows
  )
select *
From valid_values
where varc > 90;

db fiddle is here and looks like this:

Cast as number

Upvotes: 2

Related Questions