[SQL] Case insensitive select

[isaac flemmin]

Hello,

I am using PostgreSQL 7.2 and I am trying to do a case insensitive
select statement using the lower function. I am using these lines from
the PostgreSQL 7.2 users guide as a template for my query.

--
"For example, a common way to do case-insensitive comparisons is to use
the lower function:
SELECT * FROM test1 WHERE lower(col1) = 'value';"
--

The query looks almost exactly the same but it always returns 0 results.
This does not make sense to me because if the query,

"SELECT * FROM test1 WHERE col1 = 'Value';",

returns something, then I assume the query,

"SELECT * FROM test1 WHERE lower(col1) = 'value';",

should return something as well. Do I not understand the way that the
lower function works, or is there something else I have to do? Obviously
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.

Thanks
Isaac
--

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Josh Berkus]

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 majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[isaac flemmin]

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
--

Issac,

There is also:

--
-Josh Berkus

[Stephan Szabo]

On Wed, 12 Jun 2002, isaac flemmin wrote:

> Hello,
>
> I am using PostgreSQL 7.2 and I am trying to do a case insensitive
> select statement using the lower function. I am using these lines from
> the PostgreSQL 7.2 users guide as a template for my query.
>
> --
> "For example, a common way to do case-insensitive comparisons is to use
> the lower function:
> SELECT * FROM test1 WHERE lower(col1) = 'value';"
> --
>
> The query looks almost exactly the same but it always returns 0 results.
> This does not make sense to me because if the query,
>
> "SELECT * FROM test1 WHERE col1 = 'Value';",
>
> returns something, then I assume the query,
>
> "SELECT * FROM test1 WHERE lower(col1) = 'value';",
>
> should return something as well. Do I not understand the way that the
> lower function works, or is there something else I have to do? Obviously

If col1 is char(n), the above may have wierd results due to padding
spaces I believe. Does trim(lower(col1))='value' give results?

[isaac flemmin]

Yes that worked. Thanks! I will have to look more at the lower function
now so I know why that worked!

Thanks again for the prompt response.
Sincerely

Isaac

-----Original Message-----
From: pgsql-s...@postgresql.org
[mailto:pgsql-s...@postgresql.org] On Behalf Of Stephan Szabo
Sent: Wednesday, June 12, 2002 6:34 PM
To: isaac flemmin
Cc: pgsq...@postgresql.org
Subject: Re: [SQL] Case insensitive select

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org

[Josh Berkus]

Isaac,

> Yes that worked. Thanks! I will have to look more at the lower
> function
> now so I know why that worked!

It has nothing to do with the LOWER function, which is straigthforward.

You made the beginner's mistake of defining your column as CHAR and not
VARCHAR. As a result, what is actually being stored in your database
is not:
"Value"
"Value1"
but:
"Value "
"Value1 "

This is what is causing your search problems. Dump the table, and
re-build it with the correct data types (VARCHAR!). I would also
strongly reccomend that you pick up a beginner's book on SQL database
building. See:
http://techdocs.postgresql.org/techdocs/bookreviews.php

Also, unlike most other RDBMS, CHAR is *not* more efficient than
VARCHAR in Postgres.

-Josh Berkus

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)