Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-03-06T11:34:03Z

   Sean Kelly (lists@shortestpath.org) reports a bug with a severity of 1
   The lower the number the more severe it is.
   
   Short Description
   "select ... where field like lower('%text%')" fails
   
   Long Description
   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.
   
   [See code - note results where 0 rows are returned]
   
   
   
   Sample Code
   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 from user_tbl where username = '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 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 from user_tbl where company like lower('%SEaN%');
    username | company | active | created
   ----------+---------+--------+---------
   (0 rows)
   
   
   isp=> SELECT username,company,active,created from user_tbl where company like lower('SEaN%');
    username | company | active | created
   ----------+---------+--------+---------
   (0 rows)
   
   
   isp=> SELECT username,company,active,created 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)
   
   
   No file was uploaded with this report
   
   
   

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

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-03-06T15:21:35Z

   There is no bug. You want case-insensitive pattern matching, which is
   not what your call to "tolower()" accomplishes.
   
   The query
   
     SELECT company from user_tbl where company like lower('%SEaN%');
   
   will find all strings which contain "sean" (note case!). It will not
   match strings containing "Sean". Look in the PostgreSQL docs for hints
   on how to do case-insensitive searches.
   
                         - Thomas