CONCAT multiple columns in HP Vertica


someone please help me

How to concat multiple columns in Vertica

I have tried to execute ..

select ifnull(,'')|| 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: ""


  • 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',''); 
    (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 row)
    Hope this helps

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

    plz join here

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

    case when soundex( <> 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 ?


    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


    [email protected]
    [email protected]


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


    [email protected]
    [email protected]


    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 protected]
     [email protected]
    (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
     [email protected]    |
     bcde              | bcde
     [email protected] |
    (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
     [email protected]    |
     bcde              | bcde
     [email protected] |
    (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
     [email protected]    |
     [email protected] |
     bcde              | bcde
    (3 rows)
    For Soundex Functionality:

    You can find a good UDF here.

    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.

    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 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('','.',3 )   o/p: 192.128.0

    same functionality in Vertica.??

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


  •  Uday you can use the substr build-in funct:
    dbadmin=> SELECT SUBSTR('',0,10);  SUBSTR
    Also SKA wrote a UDF that will do the same exact thing - see here

    Hope this helps ! 

  • Hi Adrian oprea,

    It doesn't  work for all cases 

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

    I found the solution 

    SELECT SPLIT_PART('','.',1)||'.'||SPLIT_PART('','.',2)||'.'||SPLIT_PART('','.',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
    (3 rows)
    Using REGEXP_SUBSTR  Function

    --------------+------------- | 2.128.01 | 192.128.0     | 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 [email protected]
    I want to discuss some other imp things  plz send me mail

    Thank you

Leave a Comment

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