Re: create index with lowercase

I missed the first posts on this but
In 8i or later build an function-based index on Upper(name) then use

where upper(name) like 'AA%' for Names that start with any combination of aA ( Aa,aa,AA) or
where upper(name) like 'A%' for all names that start with A in any form

It will use the index so it should be fast...

The addition of one index should not affect, in any meaningful sense, the size of your database unless there are a very large number of rows and,even then, a single column index is not huge..

.

"Jörg Schneider" <oracle_news.3.joesch_at_spamgourmet.com> wrote:

>Hello together,
>
>thank you for your solutions but both solutions are no practicable. The
>first one makes the database to big and the second solution ist not
>felexible.
>
>thank you and i am hoping for more ways (what an english ;-))
>
>Jörg Schneider
>
>
>"Laly" <laly.kattoor_at_bnpparibas.com> schrieb im Newsbeitrag
>news:20021030-15813-423147_at_foorum.com...
>>
>> Hi,
>>
>> Otherwise you can try this :
>> if you want to look for names beginning with A :
>> select *
>> from my_table
>> where name like 'A%' or name like 'a%'
>>
>> this will use index on name.
>> You can generalize : looking for names beginning with AA :
>> where name like 'AA%' or name like 'Aa%' or name like 'aA%' or name like
>'aa%'
>> ..
>>
>>
>>
>> HTH,
>>
>>
>> Laly.
>>
>>
>> --
>> Use our news server 'news.foorum.com' from anywhere.
>> More details at: http://nnrpinfo.go.foorum.com/
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Mon Nov 04 2002 - 09:52:50 CST