如何在红移中进行动态regex匹配?

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?

那么，我有一个包含一列的表(比如a)一个“string”，另一个列具有相应的“regex模式”，是否有可能动态地检查regex是否匹配表中每一列a中的字符串值?如果没有，是否还有其他工具可以与redshift结合进行动态regex匹配?

2 个解决方案

#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.

因此，我找到了一个解决方法，结果是你不可能在redshift中进行动态regex匹配，但是你可以使用python UDF实现这一点，这是aws redshift集群提供的特性之一。

#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.

创建了上面的函数之后，below query对来自两个不同表的列进行regex匹配，并返回匹配的字符串，其中一个表有字符串，而另一个表有模式。

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