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


CONCAT multiple columns in HP Vertica — Vertica Forum

CONCAT multiple columns in HP Vertica

Hi,

someone please help me

How to concat multiple columns in Vertica

I have tried to execute ..

select ifnull(A1.name,'')|| ifnull(A1.exp_month,'')|| ifnull(A1.exp_year,'')|| ifnull(B1.firstname,''), ifnull(B1.lastname,'')|| ifnull(B1.address_1,'')|| ifnull(B1.address_2,'')||''
from .....

I'm getting an error

 [Vertica][VJDBC](3681) ERROR: Invalid input syntax for integer: ""

Comments

  • and I tried 'concat' it supports only for two columns and with same data type
  • Try to cast int into varchar.
    dbadmin=> select ifnull(1,'')||ifnull('abc','');
    ERROR 3681:  Invalid input syntax for integer: ""

    select ifnull(1::varchar,'')||ifnull('abc',''); 
    ?column?
    ----------
     1abc
    (1 row)


  • Thank you Siddarth.

    not yet cmplt my part :) 

    select count( distinct  ifnull(1::varchar,'')||ifnull('abc',''), clm1,clm2 )

    ERROR:Function count(varchar, varchar, varchar) does not exist,

    Thnx in advance

  • Navin_CNavin_C Vertica Customer
    Hello Uday,

    You cannot use a count function on more then one columns at once. but you can do a count(*)

    You are trying to use count function on 3 columns ( 1 concatenated , 2 clm1 3 clm2)

    Do you want to concatentate clm1 and clm2 as well with 1st column and then count distinct
    If so then you can do like this
    dbadmin=> select count(distinct(ifnull(1::varchar,'')||ifnull('abc','') || ifnull('def','') || ifnull('fgh',''))); count
    -------
         1
    (1 row)
    Hope this helps



  • Thank you I got the same idea :)
    yeah ..now it is working fine.

    plz join here https://www.facebook.com/groups/Vertica.Tableau/

    //You cannot use a count function on more then one columns at once. but you can do a count(*)
    But MySQL is supporting.


  • Hi Siddarth, Navin

    i'm converting MySQL code to vertica ..

    is there similar kind of soundex() function in Vertica?


    the function in WHERE class...

    where 
    case when soundex(t1.name) <> soundex(trim(t2.fname)||' '||trim(t2.lname))  .....

    I don't  want to create/import Library kind of stuff 

    could you plz suggest any easiest alternative way ?

    And 


    LOCATE(' ', name)   function in Vertica?





  • // LOCATE(' ', name)   function in Vertica?   -- INSRT()
  • There is no predefined soudex function in Vertica. You will have to create a UDF for it. I have one in C, but not sure how good it is as I hav never used it :)
  • i'm removing "use index()" function at join level I think it won't make any o/p changes ..right?
  • Ok. plz give me the code ..with proper instructions :)
  • MySQL:
    select email,right(email, length(email) - locate('@',email)) from customer

    I/P: 

    abcd@gmail.com
    bcde123@yahoo.com
    bcde


    o/p: 
    gmail.com
    yahoo.com
    bcde

    i'm converting the code to Vertica:
    select email,split_part(email,'@',2) from customer

    I/P: 

    abcd@gmail.com
    bcde123@yahoo.com
    bcde


    o/p: 
    gmail.com
    yahoo.com

    for 3rd i/p i'm getting no value so my query is not working

    any alternative?? thnx in advance



  • Navin_CNavin_C Vertica Customer
    Hello Uday,

    Three ways you can achieve this

    I have a table like this.
    nnani=> select * from NAVIN.TEST_LOCATE;       
    email
    -------------------
     abcd@gmail.com
     bcde123@yahoo.com
     bcde
    (3 rows)
    1. Using CASE statement and REGEXP_COUNT function.
    nnani=> select email, case when regexp_count(email, '@') = 1 then split_part  (email,'@',2) else email end as email_splitted from NAVIN.TEST_LOCATE;        email       | email_splitted
    -------------------+----------------
     abcd@gmail.com    | gmail.com
     bcde              | bcde
     bcde123@yahoo.com | yahoo.com
    (3 rows)
    2. Using POSITION function similar to your MySQL statement
    nnani=> select email, right(email,length(email) -  position('@' in email)) from NAVIN.TEST_LOCATE;         email       |   right
    -------------------+-----------
     abcd@gmail.com    | gmail.com
     bcde              | bcde
     bcde123@yahoo.com | yahoo.com
    (3 rows)
    3. Using INSTRB function , same as your MySQL statement
    nnani=> select email, right(email,length(email) -  INSTRB(email,'@')) from NAVIN.TEST_LOCATE;        email       |   right
    -------------------+-----------
     abcd@gmail.com    | gmail.com
     bcde123@yahoo.com | yahoo.com
     bcde              | bcde
    (3 rows)
    For Soundex Functionality:

    You can find a good UDF here.
    http://vertica-forums.com/viewtopic.php?f=80&t=1492

    Hope this helps


     



  • super :) Thank you.

    For Soundex Functionality .. I couldn't understand these steps 

    there are mainly 2 steps to do it.

    I'm running code in [Remote desktop machine] DB Visualizer  ... tell me where I have to place source code and where i have to compile compiling cmds

    Thanks in advance 
  • Navin_CNavin_C Vertica Customer
    Hello Uday,

    There are three steps in the solution on forums

    1. Compiling
    2. Example
    3. Source

    For 1st and 3rd step , we need to do it on Vertica server/cluster with dbadmin user.

    Steps.
    1. In Source part of the solution copy the complete code and put it into Soundex.cpp file (create this file first )
    2. Compile the .cpp file with the command in the compile part of solution, this will create a .so file which will be Soundex.so file
    3. This file is used by Vertica to install a UDF.
    4. Use the Example part of solution to install the Function
        a. Create library with path of .so file
        b. Create function with parameters .

    This is how you can install this UDF.

    For using it , you can use it from DBvisualiser

    Hope this helps.


  • Hi Navin,

    MySQL: select SUBSTRING_INDEX('192.128.0.15','.',3 )   o/p: 192.128.0

    same functionality in Vertica.??

    I want to run my query for all cases like ip values 1.123.0.15 [ o/p: 1.123.0 ] 

    thanks 

  •  Uday you can use the substr build-in funct:
    dbadmin=> SELECT SUBSTR('192.128.0.15',0,10);  SUBSTR
    -----------
     192.128.0
    Also SKA wrote a UDF that will do the same exact thing - see here  http://www.vertica-forums.com/viewtopic.php?f=7&t=409&p=1311&hilit=SUBSTRING_INDEX#p1311

    Hope this helps ! 

  • Hi Adrian oprea,

    It doesn't  work for all cases 

    ex: select SUBSTR('2.128.01.153',0,10);
    ------------------
      o/p: 2.128.01.

    I found the solution 

    SELECT SPLIT_PART('2.128.01.153','.',1)||'.'||SPLIT_PART('2.128.01.153','.',2)||'.'||SPLIT_PART('2.128.01.153','.',3);
    ------------
     o/p: 2.128.01
  • Navin_CNavin_C Vertica Customer
    Hi Uday,

    You can do it this way too.

    Suppose I have a table like this
    nnani=> select * from NAVIN.IP_TEST;    IP_ADDR
    --------------
     2.128.01.153
     192.128.0.15
     1.123.0.15
    (3 rows)
    Using REGEXP_SUBSTR  Function

    nnani=> SELECT IP_ADDR,REGEXP_SUBSTR(IP_ADDR,'\d+[.]\d+[.]\d+',1) as NEW_IP_ADDR FROM NAVIN.IP_TEST; 
     IP_ADDR    | NEW_IP_ADDR
    --------------+-------------
     2.128.01.153 | 2.128.01
     192.128.0.15 | 192.128.0
     1.123.0.15     | 1.123.0
    (3 rows)

    REGEXP Functions are very powerful functions in vertica, they prove to be handy many times.

    Hope this helps

  • Thank you Navin :)

    It is working fine.

    could you please share your mail id?
    or else plz drop me a text mail to princeuday9@gmail.com
    I want to discuss some other imp things  plz send me mail

    Thank you

Leave a Comment

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