Thread

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

   Sean Kelly <lists@shortestpath.org> — 2001-03-06T11:35:13Z

   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
   
   
   
   
   

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

   Randy Hall <rhall@greatbridge.com> — 2001-03-07T21:57:56Z

   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