Thread: case insensitive matching

Hi. LIKE does case sensitive string matching. Is there an easy way to do case
insensitive string matching?

Bob Kernell
Research Scientist
AS&M
email: kernell@sundog.larc.nasa.gov

On Tue, 3 Oct 2000, Robert Kernell wrote:

> Hi. LIKE does case sensitive string matching. Is there an easy way to do case
> insensitive string matching?

Use "WHERE lower(a) LIKE lower(b)" or use one of the case insensitive
regexp operators instead
(http://www.postgresql.org/docs/aw_pgsql_book/node119.html#8125)
--
Tod McQuillin

Can you use an uppercase function?

Something like: select * from table where upper(columnname) like 'SEARCH';

Can this be done in postgres?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Robert Kernell" <kernell@sundog.larc.nasa.gov>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, October 03, 2000 12:41 PM
Subject: [GENERAL] case insensitive matching


> Hi. LIKE does case sensitive string matching. Is there an easy way to do
case
> insensitive string matching?
>
> Bob Kernell
> Research Scientist
> AS&M
> email: kernell@sundog.larc.nasa.gov

Regular expression operator, ~* (Can't use indexes)

You can still use LIKE but you have to do it like :

SELECT * FROM names WHERE lower(firstname) LIKE lower('Mitch');

If you wanted to make this use indexes, you could create the index using
lower(fieldname) .. Something like :

CREATE INDEX on names lower(firstname)

Good luck!

-Mitch


----- Original Message -----
From: "Robert Kernell" <kernell@sundog.larc.nasa.gov>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, October 03, 2000 9:41 AM
Subject: [GENERAL] case insensitive matching


> Hi. LIKE does case sensitive string matching. Is there an easy way to do
case
> insensitive string matching?
>
> Bob Kernell
> Research Scientist
> AS&M
> email: kernell@sundog.larc.nasa.gov
>
>

You could use the ~* operator.  That will do a Case Insensitive
comparison.  If you go to the website
(http://www.postgresql.org/users-lounge/docs/7.0/user/) and checkout
chapter 4, you can get a good list of the operators in posgres.

At 10/3/2000 12:41 PM, you wrote:
>Hi. LIKE does case sensitive string matching. Is there an easy way to do case
>insensitive string matching?
>
>Bob Kernell
>Research Scientist
>AS&M
>email: kernell@sundog.larc.nasa.gov



--------------------------------
Doug Budny
IT - System Administrator
Due Diligence Inc.
http://www.diligence.com
Phone: (406) 728-0001
Fax: (406) 728-0006

use the '~*' operator, i.e., "attribute" ~* 'pattern'

Ragnar


-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Robert Kernell
Sent: Tuesday, October 03, 2000 17:41
To: pgsql-general@postgresql.org
Subject: [GENERAL] case insensitive matching


Hi. LIKE does case sensitive string matching. Is there an easy way to do
case
insensitive string matching?

Bob Kernell
Research Scientist
AS&M
email: kernell@sundog.larc.nasa.gov