User-Defined Functions to the Rescue
Jim Knicely authored this tip.
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 because 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-> 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)