Evaluate the similarity of two strings and return the match rate In vertica SQL

Divyashoba

Hi Guys,

I am comparing two columns in a table which contains text search. I would like to know whether we have any functions in vertica as if we have in SQL server (Soundex()) and Excel(Fuzzy lookup). Kindly help me out in this.

Sample :
Here we compare Comp_name and Company_name -and the output is similarity

Comp_name,company_name,Similarity
ALLIANCE ,alliance ia,0.9789
ARISTOON ,aristoon group,0.9556
ACII CLUB ,aciI - automobile club d'italia,0.9555
UNIVERSITTTTTA' DI MILANO , studi di milano,0.9250
START ,start srl,0.9077
ANASS ,anssa,0.9036

Thanks in advance

SruthiA

Hi DiyaShoba,

Vertica doesn't have an in-built soundex function. However you can create a UDX and use it as a function. The below github link contains .cpp code for soundex UDSF. Create a C++ UDX and use it as a function

https://github.com/sKwa/vertica/tree/master/UDx

The first step is to download the .cpp code and generate a .so file using the instructions present in the below link.

https://www.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/ExtendingVertica/C++/CompilingC++.htm

Once it is done, please run the following SQL.

CREATE LIBRARY SoundexLib AS '<.so filename>';

CREATE FUNCTION soundex AS LANGUAGE 'C++' NAME 'SoundexFactory' LIBRARY SoundexLib;

A detailed example of generating C++ User Defined Scalar Function is present in the following link

https://www.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/ExtendingVertica/UDx/ScalarFunctions/ExampleC++check_constraints.htm

Thank you,
Sruthi

Divyashoba

Hey SruthiA,
Thanks. I will try and let you know.