CODEWITHSUNDEEP
regexamazon-web-servicesamazon-redshift
krishna sai
krishna sai

Reputation: 185

How to do dynamic regex matching, in redshift?

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?

Upvotes: 7

Views: 2794

Answers (3)

Franco Piccolo
Franco Piccolo

Reputation: 7410

Let's say you have the variable column variable and you want to achieve the regex '.*variable.*' you could split your regex into two and then concatenate the splitted parts with your variable like:

'.*' + variable + '.*'

I know this is a bit hacky but it maybe less effort than going into the UDF approach.

EDIT: Actually this works only when typing the pattern in a WITH clause, not when you pull the data from a table, so this solution doesn't work.

Upvotes: 0

Kaps
Kaps

Reputation: 21

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'

Upvotes: 2

krishna sai
krishna sai

Reputation: 185

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.

Upvotes: 5

Related Questions