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

Uday_kumar

My table is look like ..i'm running the query in DB Visualizer

id     zipcode
100  200-1239
101  203-4567
105  203-9898
106  205-4564
107  225-1111
116  203-4545
126  205-8964
106  205-4564

>select id,zipcode from table1 where left(zipcode,3) in ('200','205')

the output is Empty result set. 

>select id,zipcode,left(zipcode,3) from table1
i'm getting proper result for this query.  

I need very urgent plz reply me

thanks in advance

Curtis_1

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. 

Uday_kumar

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 ...

Navin_C

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

eli_revach

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