Case insenesitive comparisons (ILIKE)

[JimKnicely]

Hi,

The LIKE SQL predicate is case sensitive.

Example:

Code: Select all

dbadmin=> create table test (name varchar(100));
CREATE TABLE
dbadmin=> insert into test values ('Jim');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('jim');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select name from test where name like '%jim%';
 name
------
 jim
(1 row)

dbadmin=> select name from test where name like '%Jim%';
 name
------
 Jim
(1 row)

If I want to do a case insensitive search, I'd use the ILIKE SQL predicate instead:

Example:

Code: Select all

dbadmin=> select name from test where name ilike '%jim%';
 name
------
 Jim
 jim
(2 rows)