CONCAT multiple columns in HP Vertica

Uday_kumar · July 2014

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: ""

Uday_kumar · July 2014

and I tried 'concat' it supports only for two columns and with same data type

Siddarth · July 2014

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)

Uday_kumar · July 2014

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_C · July 2014

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

Uday_kumar · July 2014

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.

Uday_kumar · July 2014

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?

Uday_kumar · July 2014

// LOCATE(' ', name) function in Vertica? -- INSRT()

Siddarth · July 2014

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

Uday_kumar · July 2014

i'm removing "use index()" function at join level I think it won't make any o/p changes ..right?

Uday_kumar · July 2014

Ok. plz give me the code ..with proper instructions

Uday_kumar · July 2014

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_C · July 2014

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

Uday_kumar · July 2014

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_C · July 2014

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.

Uday_kumar · July 2014

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

Adrian_Oprea_1 · July 2014

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 !

Uday_kumar · July 2014

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_C · July 2014

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

Uday_kumar · July 2014

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

CONCAT multiple columns in HP Vertica

Comments

Leave a Comment