Re: Case insensitive select statement?

Thanks Chris and Mark. I didn't realise I could use something like LOWER/UPPER on a column name! It's a shame I can't use an index for this, but according to the Oracle docs I should be able to create an index to take care of it:

create index myindex on table1 (LOWER(name))

However, Oracle8i Personal (Win98) reports that an index in a function isn't supported. Perhaps this is a restriction of the Personal edition?

Thanks again, guys.

Gary.

Mark <cdo_at_lambic.co.uk> wrote in message news:39AEAF94.22CBFAA5_at_lambic.co.uk...
> Gary wrote:
> > I'm trying to select all records beginning with the letters A, B, C, or
 D,
> > but it must be a case insensitive search.
> >
> > e.g
> > select name from table1
> > where name like 'a%'
>
> SELECT name
> FROM table1
> WHERE LOWER(name) like 'a%';
>
> This converts the name column to lower case in the where clause. The
> problem with this solution is that any index on name will not be used.
> The better way is to store the data in one case, but of course that's
> not always possible.
Received on Thu Aug 31 2000 - 17:55:18 CDT