Re: "select ... where field like lower('%text%')" fails - Mailing list 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