From: isaac flemmin <isaac@knox.net>
To: josh@agliodbs.com
To: pgsql-sql@postgresql.org
Subject: Re: Case insensitive select
Date: Wed, 12 Jun 2002 18:27:02 -0700
Message-ID: <000001c21279$6c2fcd10$ad463e04@ixic> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: isaac@knox.net, josh@agliodbs.com
Subject: Re: Case insensitive select
In-Reply-To: <000001c21279$6c2fcd10$ad463e04@ixic>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox