Conditional Where clause comparing the number value if it's a number or the string value if it's not a number

Question

I've a column field which is varchar and contains numbers and caracters entry all together.

MyColumn would contains those value for example :

"1.0", "AB", "A1", "1", "100"

If the user type "1" in my application field, I want to do a varchar search, matching "1" but also a "1.0".

---

I can't juste do a TO_NUMBER because there are data that are not a number and I would get a "not a number" exception.

---

I tried to use an OR clause checking if myColumn was a number before doing the cast to number :

(trim(TRANSLATE(myColumn,'0123456789', ' ')) is null and TO_NUMBER(myColumn) = 1.0) 
or myColumn = '1.0' 

But I still get ORA-01722: invalid number, since Oracle does not check one side of the AND clause before the other.

---

I tried to "case when" both side of the restriction :

case when trim(TRANSLATE(myColumn,'0123456789', ' ')) is null then TO_NUMBER(myColumn) else myColumn end 
= case when trim(TRANSLATE(myColumn,'0123456789', ' ')) is null then 1.0 else '1.0' end;

But I get ORA-00932 inconsistent datatypes

Is there no way to do conditional where clause in oracle ?

Answer 1

You could do this by creating a couple of functions to determine whether the value was a number or not:

create or replace function is_number (p_value varchar2)
return number
is
begin
  return to_number(p_value);
exception
  when value_error then
    return null;
end;
/

create or replace function is_not_number (p_value varchar2)
return varchar2
is
  v_num number;
begin
  v_num := is_number(p_value);
  return case when v_num is null then p_value
         end;
end;
/

And then you use those functions in the filter predicates as appropriate:

with sample_data as (select '1.0' val from dual union all
                     select 'AB' val from dual union all
                     select 'A1' val from dual union all
                     select '1' val from dual union all
                     select '100' val from dual)
select val
from   sample_data
where  is_number(val) = 1;

VAL
---
1.0
1  

with sample_data as (select '1.0' val from dual union all
                     select 'AB' val from dual union all
                     select 'A1' val from dual union all
                     select '1' val from dual union all
                     select '100' val from dual)
select val
from   sample_data
where  is_not_number(val) = 'AB';

VAL
---
AB 

It's not going to be very performant, but if you're going to shove any old data into the same column, you're not using the database to the best of its abilities.

---

If you're not allowed to add functions or change the underlying data structure (boo *{:-( ), then the following might be of use:

with sample_data as (select '1.0' val from dual union all
                     select 'AB' val from dual union all
                     select 'A1' val from dual union all
                     select '1' val from dual union all
                     select '100' val from dual union all
                     select '.100' val from dual union all
                     select '1e-10' val from dual union all
                     select '2.3e5' val from dual union all
                     select '-10' val from dual union all
                     select '+10' val from dual union all
                     select '+91.3e+2' val from dual union all
                     select '+-9.3e-2' val from dual union all
                     select '.1E5' val from dual union all
                     select '1.' val from dual union all
                     select '1.1.1' val from dual)
select val,
       case when regexp_like(trim(val), '^(-|\+)?'|| -- checks for the presence of a positive or negative sign. This applies to all subsequent checks
                                                 '([[:digit:]]+(\.[[:digit:]]*$|$)'|| -- matches bog standard numbers with or without a decimal part (eg. 1, 1.01, 1.)
                                                 '|\.{1}[[:digit:]]+$'|| -- matches a number that has no digits before the decimal point (eg. .1)
                                                 '|[[:digit:]]+\.?[[:digit:]]*(e|E)(-|\+)?[[:digit:]]+$'|| -- matches scientific numbers starting with an integer with positive or negative numbers after the e/E (eg. 1e10, 1e-10)
                                                 '|\.[[:digit:]]+(e|E)(-|\+)?[[:digit:]]+$)' -- matches scientific numbers with starting with a decimal point  (eg. .1e10, .1e-10)
                             ) then to_number(val) 
       end num,
       case when not regexp_like(trim(val), '^(-|\+)'||
                                                    '?([[:digit:]]+(\.[[:digit:]]*$|$)'||
                                                    '|\.{1}[[:digit:]]+$'||
                                                    '|[[:digit:]]+\.?[[:digit:]]*(e|E)(-|\+)?[[:digit:]]+$'||
                                                    '|\.[[:digit:]]+(e|E)(-|\+)?[[:digit:]]+$)') then val
       end txt
from   sample_data;

VAL             NUM TXT     
-------- ---------- --------
1.0               1         
AB                  AB      
A1                  A1      
1                 1         
100             100         
.100             .1         
1e-10        1.E-10         
2.3e5        230000         
-10             -10         
+10              10         
+91.3e+2       9130         
+-9.3e-2            +-9.3e-2
.1e5          10000         
1.                1         
1.1.1               1.1.1   

Be aware that I may have missed out some cases where the value is a valid number; this is the price you pay for having to replicate the functionality of to_number() manually. You'll have to add in the missing cases manually.