Re: Case insensitive select

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?