Skip site navigation (1) Skip section navigation (2)

Re: "select ... where field like lower('%text%')" fails

From: Randy Hall <rhall(at)greatbridge(dot)com>
To: Sean Kelly <lists(at)shortestpath(dot)org>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: "select ... where field like lower('%text%')" fails
Date: 2001-03-07 21:57:56
Message-ID: 3AA6AEE4.9D82FE04@greatbridge.com (view raw or flat)
Thread:
Lists: pgsql-bugs
It was doing exactly what the query told it to do.  I believe if you
rewrite your query like this, you will get the desired results.

	select ... where lower(field) like '%sometext%'

Be sure to build an index on lower(field) otherwise you will resort to
sequence scans.

Sean Kelly wrote:
> 
> Hello,
> 
>         I was trying to send the following bug report from the web page
> but it kept timing out.  I hope this is the only time it arrives on the
> list...
> 
>         I am trying to search a varchar(x) field with a query like:
> 
>                 select ... where field like lower('%someText%')
> 
>         In one field, if the value someText is at the very start then the
> search fails.  In another field, if the value someText is at the very
> start then the search succeeds.
> 
>         Here are some statements (the ones returning 0 rows should be
> returning something):
> 
> isp=> \d user_tbl
>                  Table "user_tbl"
>  Attribute |    Type     |        Modifier
> -----------+-------------+-------------------------
>  username  | varchar(10) | not null
>  company   | varchar(80) | not null
>  email     | varchar(80) |
>  password  | varchar(20) | not null
>  active    | boolean     | not null default 'TRUE'
>  created   | timestamp   | not null
> Index: user_tbl_pkey
> 
> isp=> SELECT username,company,active,created
> isp-> from user_tbl where username;
>  username |       company       | active |        created
> ----------+---------------------+--------+------------------------
>  sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
> (1 row)
> 
> isp=> SELECT username,company,active,created
> isp-> from user_tbl where username like lower('%SEaN%');
>  username |       company       | active |        created
> ----------+---------------------+--------+------------------------
>  sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
> (1 row)
> 
> isp=> SELECT username,company,active,created
> isp-> from user_tbl where company like lower('%SEaN%');
>  username | company | active | created
> ----------+---------+--------+---------
> (0 rows)
> 
> isp=> SELECT username,company,active,created
> isp-> from user_tbl where company like lower('SEaN%');
>  username | company | active | created
> ----------+---------+--------+---------
> (0 rows)
> 
> isp=> SELECT username,company,active,created
> isp-> from user_tbl where company like lower('%EaN%');
>  username |       company       | active |        created
> ----------+---------------------+--------+------------------------
>  sean     | Sean's Test Company | t      | 2001-01-14 14:01:58+00
> (1 row)
> 
>         Any advice?
> 
>         Thanks,
> 
> --
> Sean
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
---------------------------------------------------
Randy Hall                    Great Bridge, LLC
Sr. Knowledge Engineer        253 Monticello Avenue
Red Hat Certified Engineer    Norfolk, VA 23510

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-03-08 00:38:30
Subject: Re: COBOL
Previous:From: Peter EisentrautDate: 2001-03-07 20:51:00
Subject: Re: Pbm with aggregates on empty output

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group