Just to be safe I would probably recommend
WHERE lower(searchfield) LIKE lower('%he%')
however, anytime you perform an evaluation on the left side of the = you
force a table scan, which can create performance issues. This is the
problem with trying to do case insensitive searches in a DB that was
installed case sensitive. If you need to be case insensitive I suggest
reinstalling the DB in this mode and reloading your data.
Justin
> -----Original Message-----
> From: Tony Schreiber [mailto:[EMAIL PROTECTED]]
> Sent: Friday, March 15, 2002 11:46 AM
> To: CF-Talk
> Subject: RE: SQL case insensitive search ???
>
>
> Use a string function to force the search field to the same
> case as the
> search string:
>
> WHERE lower(searchfield) LIKE '%he%'
>
> > Example '%he%' finds 'the' but not 'THE'
> >
> > I would like to find regardless of case...
> >
> > How is that done?
> >
> >
> > At 09:38 PM 3/14/02 -0800, you wrote:
> > >it should be case insensitive to begin with?
> > >
> > >All '%searchString%' does is cause your query to table
> scan the whole
> > >table since the wildcard is in front.
> > >
> > >J.
> > >
> > >John Wilker
> > >Web Applications Consultant, and Author
> > >Macromedia Certified ColdFusion Developer
> > >President/Founder, Inland Empire CFUG.
> > >www.red-omega.com
> > >
> > >"more people are killed by donkeys than by airplane
> crashes each year"
> > >
> > >
> > >-----Original Message-----
> > >From: Brian Scandale [mailto:[EMAIL PROTECTED]]
> > >Sent: Thursday, March 14, 2002 9:36 PM
> > >To: CF-Talk
> > >Subject: SQL case insensitive search ???
> > >
> > >
> > >how does one go about structuring a case insensitive
> search on a text
> > >field in a database?
> > >
> > >Currently using
> > >
> > >WHERE Table.FieldName LIKE '%searchString%'
> > >
> > >Thanks,
> > >Brian
> > >
> > >
> > >
> >
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
