Search code examples
postgresqlsql-like

Postgresql pattern match a selection

I'm trying to find a selection where the start of a column matches a column in another table in postgres. I'm looking to do something along the lines of the following.

Return all records in table1 where table1.name starts with any of the labels in table2.labels.

SELECT 
    name 
FROM table1
WHERE 
    name LIKE (SELECT distinct label FROM table2);
Solution

  • You should append % sign to a label to use it in like. Also, use any() as the subquery may yield more than one row.

    select name 
from table1
where name like any(select distinct concat(label, '%') from table2);