How to use multiple values in SIMILAR TO without hardcoding-CodePudding

I have a column as follows (with imaginary values):

<table_name>.<column_name>
a_b
g_c
w_d
k_e

I would like to match this column with another column using SIMILAR TO operator. Something like:

Select *
from <table_name1>
where column_name1 SIMILAR TO '%(<table_name>.<column_name>)%';

I know the multiple values in SIMILAR TO need to be separated with a pipe operator. But I'm not sure how to do it.

Any help would be super apreciated.

EDIT: I'd need to JOIN both these tables coz I'll need one of the columns to group on. Is there a good way to do this?

CodePudding user response：

I think what you're looking for is something like

SELECT t2.*
FROM table2 t2
WHERE t2.column1 similar to '%('||(SELECT LISTAGG(t1.column1, '|') FROM table1 t1)||')%'

Note that I'm using listagg for Redshift, but in Postgre it would be string_agg.

The listagg/string_agg will concatenate all the values (first argument) together separated by a pipe (second argument). So '%('||(SELECT LISTAGG(t1.column1, '|') FROM table1 t1)||')%' will result in a string like %(a_b|g_c|w_d|k_e)% which is what you want for your similar to comparison.

An alternative to consider (especially if the values you're matching might be longer than 64K characters and cause issues with listagg) might be

SELECT distinct t2.* -- Distinct because a row in t2 might match multiple rows in t1
FROM table2 t2 INNER JOIN table1 t1
 on t2.column1 similar to '%('||t1.column1||')%' -- This could just be a normal like if you don't have any special regex besides percent and underscore