RE: SQL case insensitive search ???
It isn't case sensitive if the database hasn't been setup that way when the server was installed. This can be changed through the server management interfaces (normally). However, you can get around the issue by casting your field and your parameter to upper or lower case. i.e. Select * from My Table Where upper(Field1) = upper(@Param1) or something similar HTH -Original Message- From: John Wilker [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 10:39 PM To: CF-Talk Subject: RE: SQL case insensitive search ??? 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 the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL case insensitive search ???
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
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 > > > > > > > __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL case insensitive search ???
What database are you using? Other responses seem to have covered SQLServer. If you are using ORACLE then all searches are case sensitive and you would need to make both the column and the search criteria the same case in order to be "case-insensitive". ORACLE has two functions UPPER and LOWER that you can use, ie: WHERE UPPER(Table.FieldName) LIKE '%UPPER(searchString)%' Sam - Original Message - From: "Brian Scandale" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, March 15, 2002 12:35 AM 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 > > __ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL case insensitive search ???
the MSsql install is by default case insensitive. - Original Message - From: "John Wilker" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, March 15, 2002 12:16 PM Subject: RE: SQL case insensitive search ??? > My query returns either when searching (on my SQL box) for '%elp%' found > "Help Desk" and "HELP DESK" and '%ELP%' did likewise. > > But yeah Lcase() or Ucase() ing the string I would think would do it if > you aren't getting the same results. > > 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 10:44 PM > To: CF-Talk > Subject: RE: SQL case insensitive search ??? > > > I guess I need to explain it better then. > > Yes the % is a wildcard in front of the search string.. and behind it > too in my example > > HOWEVER, the search string itself is NOT case insensitive... so it will > only find exactmatch of searchstring > > 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 > > > > > > > > __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL case insensitive search ???
My query returns either when searching (on my SQL box) for '%elp%' found "Help Desk" and "HELP DESK" and '%ELP%' did likewise. But yeah Lcase() or Ucase() ing the string I would think would do it if you aren't getting the same results. 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 10:44 PM To: CF-Talk Subject: RE: SQL case insensitive search ??? I guess I need to explain it better then. Yes the % is a wildcard in front of the search string.. and behind it too in my example HOWEVER, the search string itself is NOT case insensitive... so it will only find exactmatch of searchstring 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 > > > __ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL case insensitive search ???
Try: WHERE Ucase(MyField) LIKE '%#Ucase(MyValue)#%' By using both the SQL and CFML versions of Ucase (or LCase() if you prefer) you effectively force case insensitivity. > I guess I need to explain it better then. > > Yes the % is a wildcard in front of the search string.. and behind it too in my example > > HOWEVER, the search string itself is NOT case insensitive... so it will only find exactmatch of searchstring > > 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 > > > > > > > __ 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=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL case insensitive search ???
I guess I need to explain it better then. Yes the % is a wildcard in front of the search string.. and behind it too in my example HOWEVER, the search string itself is NOT case insensitive... so it will only find exactmatch of searchstring 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 > > > __ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL case insensitive search ???
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
