This blog post was authored by Jim Knicely.
A friend recently asked me why we don’t have certain functions available in Vertica that are offered in databases like MySQL. I shrugged it off as we have bigger fish to fry. But he kept pushing so I showed him how easy it is to implement a user defined function in Vertica to mimic his simple MySQL function.
dbadmin=> CREATE OR REPLACE FUNCTION find_in_set(x VARCHAR, y VARCHAR) RETURN INT
dbadmin-> RETURN regexp_count(split_part(y, x, 1), ',') + 1;
dbadmin=> SELECT find_in_set(’90’, ‘10,100,90,900,90’);
dbadmin=> SELECT find_in_set(‘ank’, ‘b,ank,of,monk’);