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

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