Thread: Case insensitive select
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 --
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
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
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?
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-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Stephan Szabo Sent: Wednesday, June 12, 2002 6:34 PM To: isaac flemmin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] 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? ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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