Thread: Case sensitivity issue

If I am doing a command such as

select * from Apples where color like '%red%';

how do I make it case insensitive?

Thanks.

mike

Use: ILIKE

Michael Gay wrote:

> If I am doing a command such as
>
> select * from Apples where color like '%red%';
>
> how do I make it case insensitive?
>
> Thanks.
>
> mike

SELECT * FROM apples WHERE lower(color) LIKE '%red%';

Make sure that what ever 'red' might be is always lower case (you can pass
it through lower() too, of course)..

-Mitch


----- Original Message -----
From: "Michael Gay" <mike7411@earthlink.net>
To: <pgsql-general@postgresql.org>
Sent: Monday, September 10, 2001 12:02 AM
Subject: [GENERAL] Case sensitivity issue


> If I am doing a command such as
>
> select * from Apples where color like '%red%';
>
> how do I make it case insensitive?
>
> Thanks.
>
> mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

use "ILIKE" instead of "LIKE"
or use a regular expression match: ~* 'red'

----- Original Message -----
From: "Michael Gay" <mike7411@earthlink.net>
To: <pgsql-general@postgresql.org>
Sent: Sunday, September 09, 2001 11:02 PM
Subject: [GENERAL] Case sensitivity issue


> If I am doing a command such as
>
> select * from Apples where color like '%red%';
>
> how do I make it case insensitive?
>
> Thanks.
>
> mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

select * from Apples where lower(color) like '%red%';

ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol
Känns det oklart? Fråga på!

On 9 Sep 2001, Michael Gay wrote:

> If I am doing a command such as
>
> select * from Apples where color like '%red%';
>
> how do I make it case insensitive?
>
> Thanks.
>
> mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Szabo Zoltan <col@econet.hu> writes:
> Use: ILIKE
>
> > If I am doing a command such as
> > select * from Apples where color like '%red%';
> > how do I make it case insensitive?

I was trying to solve a similar problem where I wanted an exact match to work
in PostgreSQL and MySQL and ended up with:

        lower(Field) = lower('string')

Is there another way that works on both?
--
matthew rice <matt@starnix.com>                               starnix inc.
tollfree: 1-87-pro-linux                        thornhill, ontario, canada
http://www.starnix.com              professional linux services & products