LEFT ( clm, int) IN (, ) not working ??

Mine works.  I'm on version. 7.0.0-1

dbadmin=> select id, zip from zip ; 
id  |   zip    
-----+----------
 100 | 200-1239
 101 | 203-4567
 105 | 203-9898
 106 | 205-4564
 107 | 225-1111
 116 | 203-4545
 126 | 205-8964
 106 | 205-4564
(8 rows)

dbadmin=> select id, zip from zip where left(zip, 3) in ('200', '205') ;
 id  |   zip    
-----+----------
 100 | 200-1239
 106 | 205-4564
 106 | 205-4564
 126 | 205-8964
(4 rows)


I suggest you check your language settings. It could be influenced if your $LANG variable isn't set correctly. That's a guess, but it might be worth checking.

Hi Curtis,

now it is working ....

Sorry problem is -  

left(<column>, 3)    <column> values are in upper case letters.

now it is working --- left(lower(<column>),3) in ('abc','xyz')

I have same tables and values in MySQL and vertica ... but I couldn't checked properly in vertica, the column values are in UPPER case characters. 

Thank you ...

Hello Uday,

Vertica is a case sensitive search  database.
It searches data in a case sensitive manner.

If you want Vertica to be case insensitive while searching then you need to change the locale of the session / database.

However the above workaround is always suggested while comparing predicates in vertica to values.

Two Workaround:
1. User UPPER / LOWER Function on both sides of your predicates

2. Use locale LEN_S1 for session or database.( This works in some cases only)

Hope this helps

In addtion to navin replay ,#3 is "Ilike" operator which can be in some cases fast then #1 & #2.