Vertica Quick Tip: User Defined Functions to the Rescue

Posted May 7, 2018 by Soniya Shah, Information Developer

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. Example: dbadmin=> CREATE OR REPLACE FUNCTION find_in_set(x VARCHAR, y VARCHAR) RETURN INT dbadmin-> AS dbadmin-> BEGIN dbadmin-> RETURN regexp_count(split_part(y, x, 1), ',') + 1; dbadmin-> END; CREATE FUNCTION dbadmin=> SELECT find_in_set(’90’, ‘10,100,90,900,90’); find_in_set ————- 3 (1 row) dbadmin=> SELECT find_in_set(‘ank’, ‘b,ank,of,monk’); find_in_set ————- 2 (1 row) Have Fun!