We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


LEFT ( clm, int) IN (, ) not working ?? — Vertica Forum

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