Invalid Number Error! Can't seem to get around

Question

Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uid field is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uid of '2-lkjsdf' and another might be 1234567890.

I want to return just the rows with valid numbers in person_uid. The SQL I am trying is...

select person_uid 
from s_contact 
where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'

The translate replaces all numbers with spaces so that a trim will result in null if the field only contained numbers. Then I use a decode statement to set a little code to filter on. n=number, c=char.

This seems to work when I run just a preview, but I get an 'invalid number' error when I add a filter of...

and person_uid = 100
-- or
and to_number(person_uid) = 100

I just don't understand what is happening! It should be filtering out all the records that are invalid numbers and 100 is obviously a number...

Any ideas anyone? Greatly Appreciated!

Answer 1

Regular expressions to the rescue!

where regexp_like (person_uid, '^[0-9]+$')

Answer 2

Use the first part of your query to generate a temp table. Then query the temp table based on person_uid = 100 or whatever.

The problem is that oracle tries to convert each person_uid to an int as it gets to it due to the additional and statement in your where clause. This behavior may or may not show up in the preview depending on what records where picked.

Answer 3

When you add those numbers to the WHERE clause it's still doing those checks. You can't guarantee the ordering within the WHERE clause. So, it still tries to compare 100 to '2-lkjsdf'.

Can you use '100'?

Answer 4

Unfortunately, the various subquery approaches that have been proposed are not guaranteed to work. Oracle is allowed to push the predicate into the subquery and then evaluate the conditions in whatever order it deems appropriate. If it happens to evaluate the PERSON_UID condition before filtering out the non-numeric rows, you'll get an error. Jonathan Gennick has an excellent article Subquery Madness that discusses this issue in quite a bit of detail.

That leaves you with a few options

1) Rework the data model. It's generally not a good idea to store numbers in anything other than a NUMBER column. In addition to causing this sort of issue, it has a tendency to screw up the optimizer's cardinality estimates which leads to less than ideal query plans.

2) Change the condition to specify a string value rather than a number. If PERSON_UID is supposed to be a string, your filter condition could be PERSON_UID = '100'. That avoids the need to perform the implicit conversion.

3) Write a custom function that does the string to number conversion and ignores any errors and use that in your code, i.e.

CREATE OR REPLACE FUNCTION my_to_number( p_arg IN VARCHAR2 )
  RETURN NUMBER
IS
BEGIN
  RETURN to_number( p_arg );
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;

and then my_to_number(PERSION_UID) = 100

4) Use a subquery that prevents the predicate from being pushed. This can be done in a few different ways. I personally prefer throwing a ROWNUM into the subquery, i.e. building on OMG Ponies' solution

WITH valid_persons AS (
  SELECT TO_NUMBER(c.person_uid) 'person_uid',
         ROWNUM rn
    FROM S_CONTACT c
   WHERE REGEXP_LIKE(c.personuid, '[[:digit:]]'))
SELECT *
  FROM valid_persons vp
 WHERE vp.person_uid = 100

Oracle can't push the vp.person_uid = 100 predicate into the subquery here because doing so would change the results. You could also use hints to force the subquery to be materialized or to prevent predicate pushing.

Answer 5

Another option is to apply a subselect

SELECT * FROM (
 select person_uid 
 from s_contact 
 where decode(trim(translate(person_uid, '1234567890', ' ')), null, 'n', 'c') = 'n'
)
WHERE TO_NUMBER(PERSON_UID) = 100

Answer 6

Another alternative is to combine the predicates:

where case when translate(person_uid, '1234567890', ' ')) is null 
  then to_number(person_uid) end = 100