Case Insensitive String Search

ilikeuThere are times while searching column data in tables that we’ll only know a portion of the actual value we desire to find. For these types of queries we can use the LIKE predicate.

The LIKE predicate retrieves rows where the string value of a column matches a specified pattern which can contain one or more wildcard characters.

By default in MySQL and SQL Server, the LIKE operand will perform a case insensitive search.

For instance, in MySQL the following query will return one row:

MySQL> SELECT 'I''m insensitive' AS "Compare Type"
    ->   FROM DUAL
    ->  WHERE 'SuMmEr Is ComIng' LIKE 'summer is coming';
+-----------------+
| Compare Type    |
+-----------------+
| I'm insensitive |
+-----------------+
1 row in set (0.00 sec)

Likewise, in SQL Server the following query also returns one row:

sql-server_like

However, in Vertica the default behavior of the LIKE operand is to be case sensitive! So the following query will return no rows:

dbadmin=> SELECT 'I''m insensitive' AS "Compare Type"
dbadmin->     FROM DUAL
dbadmin->  WHERE 'SuMmEr Is ComIng' LIKE 'summer is coming';
Compare Type
--------------
(0 rows)

To perform a case insensitive search in Vertica, you will need to use the ILIKE operand instead.

dbadmin=> SELECT 'I''m insensitive' AS "Compare Type"
dbadmin->      FROM DUAL
dbadmin->  WHERE 'SuMmEr Is ComIng' ILIKE 'summer is coming';
Compare Type
--------------

I’m insensitive
(1 row)

Have fun!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.