SQL Invalid number

Question

1) When I run below query , DB is throwing an SQL error i.e.,

'Invalid Number'

SELECT b.col1,b.col2 FROM table b WHERE b.col1=123 AND b.col2 like '%0%'

2) But DB is not throwing any error when I use any value other than (0-10) in like operator

SELECT b.col1,b.col2 FROM table b WHERE b.col1=123 AND b.col2 like '%59%'

Both col1 and col2 are VARCHAR2

Answer 1

Error while converting varchar2 to int

You should write

SELECT b.col1,b.col2 FROM table b WHERE b.col1='123' AND b.col2 like '%0%'

your col1 of table may contain leading or trailing space for 0-10

Answer 2

Although in your query the predicate on col1 is listed before that on col2, it doesn't mean that it gets applied to the system in that order.

Even if you were performing a full table scan in which every row is tested to see if the predicates are all true for it, the predicates are tested one at a time. In general the optimiser will try to execute the predicate that consumes fewer resources (primarily CPU) first, and if that predicate is false then no others need be executed.

In this case either:

1. You're getting a full table scan, but the like on col2 is always executed first because there is no implicit data format conversion involved in it (guess 1), or because the optimiser recognises that it might throw an error of the type you're seeing (guess 2), or ...
2. You're getting an index scan on col2 (which seems unlikely, to be honest) and only rows which meet that condition are then accessed and the predicate on col1 then evaluated on them.

Either way, it seems that none of the rows for which the col2 predicate is true have a value for col1 that cannot be implicitly converted to a number.

What you should of course be doing is:

WHERE b.col1='123' AND b.col2 like '%59%'

Implicit data conversions are not a good thing.

Answer 3

This fails:

SELECT b.col1,b.col2 FROM table b WHERE b.col1=123 AND b.col2 like '%0%'

... because some of the rows having col2 like '%0%' have col1 values that cannot be converted to numbers.

This doesn't fail:

SELECT b.col1,b.col2 FROM table b WHERE b.col1=123 AND b.col2 like '%59%'

... because none of the rows having col2 like '%59%' have col1 values that cannot be converted to numbers.

But, you're just getting lucky in the second case. If col1 is a VARCHAR2 field, you should compare it to a string. I.e.,

WHERE b.col1='123'