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

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




Comments

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



  • Navin_CNavin_C Vertica Customer
    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file