Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Please HELP
Hi
Yes, the solution would work but using UPPER(THENAME) in the WHERE clause would prohibit the use of any index on the THENAME column ( forcing a full table scan). You could add another column called THENAME_UPPER which is populated with the THENAME in capitals on insert ( e.g. by a trigger ). Index this column and you could then use the select below:
SELECT * FROM MYTABLE WHERE THENAME_UPPER LIKE '%WOOD%'; Hope this helps
Mike
In article <367790AE.6427_at_teleport.com>,
GHouck <hksys_at_teleport.com> wrote:
> Steven wrote:
> >
> > hi,
> >
> > l'm using "LIKE" for for my search engine,
> >
> > May l know, is there any way to make the search result
> >
> > non case sensitive.
> >
> > e.g
> >
> > If my database's has a value named "Wood",
> >
> > when l do a search on this keyword "wood".
> >
> > " Wood" would be one of the potential answer.
> >
> > Thank You.
> >
> > --
> > With regards
> > Steven
> >
> > Steven Koh, Internet Software Engineer,
> > Mediaworks, Pacific Internet Pte. Ltd.,
> > 77 Science Park Drive, 04-09/12
> > Cintech III, Singapore Science Park,
> > Singapore 118256.
> >
> > Tel: (65) 7711305;
> > Fax: (65) 8728523
> > www.mediaworks.com.sg, www.pacific.net.sg
>
> Steven,
>
> It is my understanding (i.e., pre-8.x) that you have
> to do something like (pardon the pun):
>
> SELECT * FROM MYTABLE WHERE UPPER(THENAME) LIKE '%WOOD%';
>
> If 'wood' is the entire string, then you don't need the 'LIKE':
>
> SELECT * FROM MYTABLE WHERE UPPER(THENAME) = 'WOOD';
>
> Yours,
>
> Geoff Houck
> systems hk
> hksys_at_teleport.com
> http://www.teleport.com/~hksys
>
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Dec 16 1998 - 10:50:25 CST