CONCAT multiple columns in HP Vertica

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

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

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

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

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