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