Case-Insensitive Session Queries

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser, VerticaPartners

Jim Knicely authored this tip.

By default, Vertica is case sensitive when it comes to comparing strings. You can change this behavior by setting the session locale to LEN_S1.

Example:

dbadmin=> CREATE TABLE test (c1 VARCHAR(25));
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 'Vertica Rocks!';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test WHERE c1 = 'VERTICA ROCKS!';
 c1
----
(0 rows)

dbadmin=> SHOW locale;
  name  |               setting
--------+--------------------------------------
 locale | [email protected]=binary (LEN_KBINARY)
(1 row)

dbadmin=> SET LOCALE TO LEN_S1;
INFO 2567:  Canonical locale: 'en'
Standard collation: 'LEN_S1'
English
SET
dbadmin=> SELECT * FROM test WHERE c1 = 'VERTICA ROCKS!';
       c1
----------------
 Vertica Rocks!
(1 row)

Note that you will see a pretty hefty performance hit when using the LEN_S1 locale. A better option would be to store your data in all upper case or all lower case…

dbadmin=> ALTER TABLE test ADD COLUMN c2 VARCHAR(25) DEFAULT UPPER(c1);
ALTER TABLE

dbadmin=> ALTER TABLE test ADD COLUMN c3 VARCHAR(25) DEFAULT LOWER(c1);
ALTER TABLE

dbadmin=> SELECT * FROM test WHERE c2 = 'VERTICA ROCKS!';
       c1       |       c2       |       c3
----------------+----------------+----------------
 Vertica Rocks! | VERTICA ROCKS! | vertica rocks!
(1 row)

dbadmin=> SELECT * FROM test WHERE c3 = 'vertica rocks!';
       c1       |       c2       |       c3
----------------+----------------+----------------
 Vertica Rocks! | VERTICA ROCKS! | vertica rocks!
(1 row)

Have Fun!

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited March 29

    Make sure to heed the warning in the tip!

    Note that you will see a pretty hefty performance hit when using the LEN_S1 locale

    I get asked a lot if Vertica can be made to be case insensitive like SQL Server and MySQL. The answer to that is yeah, but is it a good idea? No... Mainly cause it'll wreak havoc on the query plans and they'll certainly be unoptimized. But I wanted to show you that Vertica can support case insensitivity :p

Sign In or Register to comment.