Re: Case insensitive select statement?

Thanks Steve.

> where upper(substring(name,1,1)) in ('A','B','C','D')
It stumped me for a minute then I realised you meant "substr" instead! This is a very neat way of writing the query, compact and readable at a glance. All of the examples you gave returned very roughly the same speed results, about 450 to 600ms to complete the query, although the last 2 methods occassionally took just 180ms.

This is my first time using Oracle and I'm baffled why Access is 2 to 100 times faster performing the same queries on the same data! I realise that Oracle will handle heavy loads and Access will fall over at some point, but I'm using a lot of web-application/server-side caching so the database won't have to handle so many requests (except for free text searches). That aside, there must be some way to speed up Oracle? It just seems odd that it's so slow. (I have 2 set ups, one with a local Oracle database and another with a remote Oracle server. Both are slower than Access).

Anyway, I don't wish to side track you with other matters! Thanks for your help with the queries.

Gary.

Steve McDaniels <steve.mcdaniels_at_sierra.com> wrote in message news:8omlcd$sao$1_at_spiney.sierra.com...
> (this is good for small tables, requires a full-table-scan, ignores index
 on
> name)
>
> select <fields> from <table>
> where upper(substring(name,1,1)) in ('A','B','C','D')
> (this is good for small tables)
>
>
> Or
>
> (this is better for larger tables, works ok if name is indexed)
>
> select <fields> from <table>
> where upper(name) like 'A%'
> or upper(name) like 'B%'
> or upper(name) like 'C%' etc.
>
> Or
>
> (this is best for very large tables, assuming index on name)
>
> select <fields> from <table>
> where Name like 'A%'
> or Name like 'B%'
> or Name like 'b%'
> or Name like 'C%'
> or Name like 'c%' etc.
>
>
> "Gary" <garygfx_at_hotmail.com> wrote in message
> news:8om9dj$b86$1_at_uranium.btinternet.com...
> > 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%'
> >
> > How can I make it case insensitive please? I'm also trying to select all
> > records that contain a phrase, such as "upper or lower case" but again
 it
> > must be case insensitive.
> >
> > eg.
> > select name from table1
> > where name like '%upper or lower case%'
> >
> > Unlike Access, Oracle 8i seems to be case sensitive when selecting
 records.
> > Any suggestions please?
> >
> > Thanks,
> > Gary.
> >
> >
> >
>
>
Received on Fri Sep 01 2000 - 05:32:33 CDT