How to do dynamic regex matching, in redshift?

Question

So, I have a table with one of columns(say A) a "string", and another column having the corresponding "regex pattern", is it possible to check if the regex matches string value in column A for every column in the table dynamically? If not, is there any other tool which I can integrate with redshift to do dynamic regex matching?

Answer 1

So, I found a work around for this, turns out there is no way you can do a dynamic regex matching in redshift, but you can achieve this using python UDF, one of the features aws redshift cluster provides.

Answer 2

CREATE OR REPLACE FUNCTION regex_match(input_str character varying, in_pattern character varying)
RETURNS character varying AS
'import re
if re.match(in_pattern,input_str):
       a=input_str
else:
       a ="False"
return(a)
End'
LANGUAGE plpythonu STABLE;

Once we create above function, below query does a regex match on columns from two different tables and returns matched strings where one table has strings and other table has patterns.

select distinct regex_match from 
(select  regex_match(t1.col1,t2.col2) as regex_match
from t1, t2)
where regex_match<>'False'