Redshift table joins using Similar To

Question

I have this query that runs in Redshift:

Select A.* 
From TableA A 
join TableB B 
on A.Col like B.Pattern

Where the Pattern column in TableB looks like:

('%foo%', '%bar%', ...)

TableB has hundreds of pattern rules and is used in several locations to its easier and cleaner to maintain one table of string rules.

Is there a way to use similar to instead of like in the join condition in Redshift? I.e. Select A.* From TableA A join TableB B on A.Col similar to B.Pattern

When I try the above query, I get this error:

Specified types or functions (one per INFO message) not supported on Redshift tables.

Answer 1

Works fine for me:

with cte1 AS (
    select 1 AS id, 'ABC'::varchar as col
    union
    select 2 AS id, 'BDE'::varchar AS col
), cte2 AS (
    select '%BC%'::varchar AS patt
)
select a.*
from cte1 a
inner join cte2 b
    ON a.col similar to b.patt