Re: Case insensitive select statement?

(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 Thu Aug 31 2000 - 17:16:32 CDT