Case insenesitive comparisons (ILIKE)

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Case insenesitive comparisons (ILIKE)

Post by JimKnicely » Thu Mar 01, 2012 4:54 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”