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

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
someTextis 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

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