Обсуждение: non-case sensitive searches

Currently I have a select statement like so:

select * from photos where keywords LIKE '%$cat_name%'

The above select statement works, except that it is case sensitive.
Is there a way to do this that is not case sensitive?


Kevin



--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

On Wed, 13 Jan 1999, Kevin Heflin wrote:
> Currently I have a select statement like so:
>
> select * from photos where keywords LIKE '%$cat_name%'
>
> The above select statement works, except that it is case sensitive.
> Is there a way to do this that is not case sensitive?


Sorry for the lame question, I found a suggestion on some SQL mailing list
archive which suggested using:

SELECT * from photos were lower(keywords) LIKE lower('%$cat_name%'






--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Hi Kevin,

You can also use regexp expression like this :

    SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';

it works fine.

Gilles

Kevin Heflin wrote:

> Currently I have a select statement like so:
>
> select * from photos where keywords LIKE '%$cat_name%'
>
> The above select statement works, except that it is case sensitive.
> Is there a way to do this that is not case sensitive?
>
> Kevin
>
> --------------------------------------------------------------------
> Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
> VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
> kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
> --------------------------------------------------------------------

On Wed, 13 Jan 1999, Gilles Darold wrote:

> Hi Kevin,
>
> You can also use regexp expression like this :
>
>     SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';
>
> it works fine.


Thanks for the tip. Any recommendations as to which would be faster when
searching about 2,000 records or so..

WHERE keywords ~* '.*$cat_name.*';

or

WHERE lower(keywords) LIKE lower('%$cat_name%')


Thanks again.


--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Actually, There is a far better way to do that, the exact operator
escapes me right now... but its in the postgres documentation.

On Wed, 13 Jan 1999, Kevin Heflin wrote:

> On Wed, 13 Jan 1999, Kevin Heflin wrote:
> > Currently I have a select statement like so:
> >
> > select * from photos where keywords LIKE '%$cat_name%'
> >
> > The above select statement works, except that it is case sensitive.
> > Is there a way to do this that is not case sensitive?
>
>
> Sorry for the lame question, I found a suggestion on some SQL mailing list
> archive which suggested using:
>
> SELECT * from photos were lower(keywords) LIKE lower('%$cat_name%'
>
>
>
>
>
>
> --------------------------------------------------------------------
> Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
> VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
> kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
> --------------------------------------------------------------------
>
>
>

>
> On Wed, 13 Jan 1999, Gilles Darold wrote:
>
> > Hi Kevin,
> >
> > You can also use regexp expression like this :
> >
> >     SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';
> >
> > it works fine.
>
>
> Thanks for the tip. Any recommendations as to which would be faster when
> searching about 2,000 records or so..
>
> WHERE keywords ~* '.*$cat_name.*';
>
> or
>
> WHERE lower(keywords) LIKE lower('%$cat_name%')

Well, you could (1) omit the second "lower()" function
(like '%cat_name%' is already in lower case!) (2) insure that your
table already contains nothing but lower-case (or upper-case) entries,
if appropriate.

- Tim

> > You can also use regexp expression like this :
> >
> >     SELECT * FROM photos WHERE keywords ~* '.*$cat_name.*';
> >
> > it works fine.
>
>
> Thanks for the tip. Any recommendations as to which would be faster when
> searching about 2,000 records or so..

LIKE is faster, but with 2,000 records, you won't be able to tell the
difference. It takes a blink.

--Gene