Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please HELP

Re: Please HELP

From: <mike_pinker_at_iint.com>
Date: Wed, 16 Dec 1998 16:50:25 GMT
Message-ID: <758och$mt9$1@nnrp1.dejanews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US