Case Insensitive String Search
April 17, 2013 
0 Comments
There 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:
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 DUALdbadmin-> 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 DUALdbadmin-> WHERE 'SuMmEr Is ComIng' ILIKE 'summer is coming';
Compare Type
--------------I’m insensitive
(1 row)
Have fun!
No comments yet... Be the first to leave a reply!