From: "isaac flemmin" <isaac@knox.net>
To: <josh@agliodbs.com>, <pgsql-sql@postgresql.org>
Subject: Re: Case insensitive select
Date: Wed, 12 Jun 2002 18:27:02 -0700
Message-ID: <000001c21279$6c2fcd10$ad463e04@ixic>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <200206121808.19116.josh@agliodbs.com>

Thanks for the prompt reply, but while I was waiting for a reply I
discovered that using the RE operator '~*' will give me results, but if
in col1 there is are both 'value', and 'value1' and I look for value by
doing

SELECT * FROM test1 WHERE col1 ~* '^value';

I get both of them, value, and value1. 

(I freely admit I do not know much about regular expressions so I will
continue to read about them and see if I can get something to work for
me, but while I am working on that any more advice would be helpful!)

Also I did try ILIKE before I wrote the email and it did not work for me
unless I would put a % at the end of the string, eg.

SELECT * FROM test1 WHERE col1 ILIKE 'value';

would not return any results, but

SELECT * FROM test1 WHERE col1 ILIKE 'value%';

would return both the rows with value and value1. Neither of these are
acceptable, because I only want to find exact matches that are case
insensitive. I have been reading the documentation on operators and
functions but as of yet, have not discovered anything that will work
correctly 100% of the time. So once again I would appreciate any ideas
on why this may not be working for me.

Thanks again for the reply.
Isaac
--

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Wednesday, June 12, 2002 6:08 PM
To: isaac flemmin; pgsql-sql@postgresql.org
Subject: Re: [SQL] Case insensitive select


Issac,

> I am doing something wrong or it would be working. I really have no
idea
> how to do a case insensitive query other than with the lower function.
> If there is anyone out there who has any ideas on how to help me or
can
> point me toward something that will help me it would be very
> appreciated.

There is also:

SELECT * FROM test1 WHERE col1 ~* '^value';
or:
SELECT * FROM test1 WHERE col1 ILIKE 'value';

See Operators and Functions in the online docs.

-- 
-Josh Berkus


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly