Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: "select ... where field like lower('%text%')" fails
Date: 2001-03-06 11:34:03
Message-ID: 200103061134.f26BY3s53563@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Sean Kelly (lists(at)shortestpath(dot)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


Responses

pgsql-bugs by date

Next:From: Sean KellyDate: 2001-03-06 11:35:13
Subject: "select ... where field like lower('%text%')" fails
Previous:From: pgsql-bugsDate: 2001-03-06 11:34:00
Subject: "select ... where field like lower('%text%')" fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group