Re: Problem : Doing case-insensitive queries...

How about writing a function to be used with your where clause in the select statement?
select name from MyTab where name = MyFunc(name,'bill');

create or replace function MyFunc(name varchar2, myparam varchar2) return varchar2 is
begin
  if lower(name) = lower(myparam) then
    return(name);
  else
   return(myparam);
 end if;
end;
/

Thorsten Redicker <TRedicker_at_LP-Software.de> wrote in article <3444bc01.22996827_at_personalnews.germany.eu.net>...
> Hi all,
>
> I have to select several lines out of a very large Table. The
> SQL-Statement goes like this:
>
> select name from MyTab where name = 'bill'; (simplified)
>
> For more comfort we have an index on column name.
>
> The Problem is that I need to select those lines
> _case-insensitive_. That meens I need to select lines
> with 'bill','Bill' and some stupid users insert 'bIlL'.
>
> Therefore my select-statement was changed to:
>
> select name from MyTab where upper(name) = 'BILL'; (still simplified)
>
> This statement lasts about 4 times longer than the first one.
> Is there a way to avoid using UPPER but still to select
> case-insensitive ?
>
> Please answer via EMail to BKeirinn_at_lp-software.de.
>
> Thanks in advance.
>
> --
> ------------------------------------------------------
> Thorsten Redicker
>
> EMail: thorsten_at_ankh.ping.de (private only)
> TRedicker_at_LP-Software.com
>
> Lenz und Partner GmbH
> Königswall 21 (Harenberg City-Center)
> D-44137 Dortmund
>
Received on Wed Oct 15 1997 - 00:00:00 CDT