Case Insensitive Equality Searches

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Case Insensitive Equality Searches

Eric Pankoke
If I want to do "SELECT * FROM table WHERE field = value", how can I do this matching text only and not case?

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

D. Richard Hipp
[hidden email] wrote:
> If I want to do "SELECT * FROM table WHERE field = value",
> how can I do this matching text only and not case?
>

   SELECT * FROM table
    WHERE field COLLATE nocase = value;

--
D. Richard Hipp  <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

Puneet Kishor-2
In reply to this post by Eric Pankoke
On 3/8/07, [hidden email] <[hidden email]> wrote:
> If I want to do "SELECT * FROM table WHERE field = value", how can I do this matching text only and not case?
>


you can do it in your application using the language of your choice by
upcasing or downcasing your field and value. Here is a Perl example

SELECT * FROM table WHERE upper(field) = uc($value)


or, you can define your column (done at CREATE TABLE time) with a
COLLATE NOCASE option. See
<http://www.sqlite.org/datatype3.html#collation>

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

Igor Tandetnik
In reply to this post by Eric Pankoke
[hidden email] wrote:
> If I want to do "SELECT * FROM table WHERE field = value", how can I
> do this matching text only and not case?

If you always need case insensitive comparison, the easiest way is to
assign NOCASE collation to the field when creating the table:

create table mytable (field char collate NOCASE, ...)

If you need only this comparison to be case insensitive, you can do

SELECT * FROM table WHERE upper(field) = upper(value);

Reportedly, the latest CVS code for SQLite also supports

SELECT * FROM table WHERE field=value collate NOCASE;

This will probably make it into the next release.

Note that NOCASE collation in SQLite only recognizes letters A through Z
as being equal to a through z. It doesn't support any other characters,
e.g. accented Latin characters or characters from other scripts. If you
need any kind of linguistically correct collation, you need to provide
one yourself (luckily SQLite supports custom collations).

Igor Tandetnik


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

Puneet Kishor-2
In reply to this post by D. Richard Hipp
On 3/8/07, [hidden email] <[hidden email]> wrote:
> [hidden email] wrote:
> > If I want to do "SELECT * FROM table WHERE field = value",
> > how can I do this matching text only and not case?
> >
>
>    SELECT * FROM table
>     WHERE field COLLATE nocase = value;


ahhh, I didn't know this could be done at SELECT time. I thought this
had to be defined at CREATE TABLE time. Thanks for the clarification.

Once again, however, it perplexes me how one could have found this out
from the existing docs. The more that can be found from existing docs,
the less folks will have to rely on the mailing list for help
(hopefully).

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

Eric Pankoke
In reply to this post by Eric Pankoke
Thank you.  I'm sure this has been addressed before, but I couldn't remember where the list archive was.  You'd think as long as I've been on this list I'd have known the answer to this anyway :)

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -------------- Original message ----------------------
From: [hidden email]

> [hidden email] wrote:
> > If I want to do "SELECT * FROM table WHERE field = value",
> > how can I do this matching text only and not case?
> >
>
>    SELECT * FROM table
>     WHERE field COLLATE nocase = value;
>
> --
> D. Richard Hipp  <[hidden email]>
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: Case Insensitive Equality Searches

Samuel Neff
In reply to this post by Puneet Kishor-2

Actually converting everything to upper-case (or lower-case) does not
achieve case-insensitive comparison in all locales.

Here's a thread on the subject with examples in C#, but assuming consistent
i18n support the issue would affect all programming environments.

http://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/brow
se_thread/thread/23b40b19185821d9/9a60db731adb2159
 
Sam


-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [hidden email]
 
-----Original Message-----
From: P Kishor [mailto:[hidden email]]
Sent: Thursday, March 08, 2007 11:06 AM
To: [hidden email]
Subject: Re: [sqlite] Case Insensitive Equality Searches

On 3/8/07, [hidden email] <[hidden email]> wrote:
> If I want to do "SELECT * FROM table WHERE field = value", how can I do
this matching text only and not case?
>


you can do it in your application using the language of your choice by
upcasing or downcasing your field and value. Here is a Perl example

SELECT * FROM table WHERE upper(field) = uc($value)


or, you can define your column (done at CREATE TABLE time) with a
COLLATE NOCASE option. See
<http://www.sqlite.org/datatype3.html#collation>

--
Puneet Kishor http://punkish.eidesis.org/


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

D. Richard Hipp
In reply to this post by Puneet Kishor-2
"P Kishor" <[hidden email]> wrote:

> On 3/8/07, [hidden email] <[hidden email]> wrote:
> > [hidden email] wrote:
> > > If I want to do "SELECT * FROM table WHERE field = value",
> > > how can I do this matching text only and not case?
> > >
> >
> >    SELECT * FROM table
> >     WHERE field COLLATE nocase = value;
>
>
> ahhh, I didn't know this could be done at SELECT time. I thought this
> had to be defined at CREATE TABLE time. Thanks for the clarification.
>
> Once again, however, it perplexes me how one could have found this out
> from the existing docs. The more that can be found from existing docs,
> the less folks will have to rely on the mailing list for help
> (hopefully).
>

It's a new feature, introduced with 3.3.13 and still experimental,
so undocumented.  :-)
--
D. Richard Hipp  <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Re: Case Insensitive Equality Searches

Eric Pankoke
In reply to this post by Eric Pankoke
My first suggestion was using upper around both operands in the equation, but the person who needed it said that didn't work, which actually leads me to another question.  Is any of this stuff (upper, collate nocase, etc) available in 2.x, or are they 3.x innovations?

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -------------- Original message ----------------------
From: "Igor Tandetnik" <[hidden email]>

> [hidden email] wrote:
> > If I want to do "SELECT * FROM table WHERE field = value", how can I
> > do this matching text only and not case?
>
> If you always need case insensitive comparison, the easiest way is to
> assign NOCASE collation to the field when creating the table:
>
> create table mytable (field char collate NOCASE, ...)
>
> If you need only this comparison to be case insensitive, you can do
>
> SELECT * FROM table WHERE upper(field) = upper(value);
>
> Reportedly, the latest CVS code for SQLite also supports
>
> SELECT * FROM table WHERE field=value collate NOCASE;
>
> This will probably make it into the next release.
>
> Note that NOCASE collation in SQLite only recognizes letters A through Z
> as being equal to a through z. It doesn't support any other characters,
> e.g. accented Latin characters or characters from other scripts. If you
> need any kind of linguistically correct collation, you need to provide
> one yourself (luckily SQLite supports custom collations).
>
> Igor Tandetnik
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Case Insensitive Equality Searches

D. Richard Hipp
[hidden email] wrote:
> My first suggestion was using upper around both operands in the equation, but the person who needed it said that didn't work, which actually leads me to another question.  Is any of this stuff (upper, collate nocase, etc) available in 2.x, or are they 3.x innovations?
>

The collation stuff is all new to 3.x.  I don't recall with
certainty if upper() was available in 2.x or not, but I think
it was.
--
D. Richard Hipp  <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------