CODEWITHSUNDEEP
postgresqlsql-like
Razzildinho
Razzildinho

Reputation: 2584

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);

Upvotes: 0

Views: 149

Answers (2)

Dimitri Fontaine
Dimitri Fontaine

Reputation: 266

You can join data from tables on any operator you want, including for example the regexp matching operator ~.

begin;

create table so.a(f1 text);
create table so.b(f2 text);

insert into so.a(f1)
      select md5(x::text)
        from generate_series(1, 300) t(x);

insert into so.b(f2)
      select substring(md5(x::text) from (20*random())::int for 4)
        from generate_series(1, 20) t(x);

   select f2, f1
     from      so.a
          join so.b
             on a.f1 ~ b.f2
 order by f2;

rollback;

Which gives:

pgloader# \i /Users/dim/dev/temp/stackoverflow/45693581.sql
BEGIN
CREATE TABLE
CREATE TABLE
INSERT 0 300
INSERT 0 20
  f2  │                f1                
══════╪══════════════════════════════════
 12bd │ 6512bd43d9caa6e02c990b0a82652dca
 3708 │ 98f13708210194c475687be6106a3b84
 4d76 │ c20ad4d76fe97759aa27a0c99bff6710
 5d77 │ e4da3b7fbbce2345d7772b0674a318d5
 5f74 │ 1f0e3dad99908345f7439f8ffabdffc4
 5fce │ 8f14e45fceea167a5a36dedd4bea2543
 6790 │ 1679091c5a880faf6fb5e6087eb1b2dc
 6802 │ d3d9446802a44259755d38e6d163e820
 6816 │ 6f4922f45568161a8cdf4ad2299f6d23
 74d9 │ c74d97b01eae257e44aa9d5bade97baf
 7ff0 │ 9bf31c7ff062936a96d3c8bd1f8f2ff3
 820d │ c4ca4238a0b923820dcc509a6f75849b
 87e4 │ eccbc87e4b5ce2fe28308fd9f2a7baf3
 95fb │ c9f0f895fb98ab9159f51fd0297e236d
 aab  │ 32bb90e8976aab5298d5da10fe66f21d
 aab  │ aab3238922bcc25a6f606eb525ffdc56
 aab  │ d395771085aab05244a4fb8fd91bf4ee
 c51c │ 45c48cce2e2d7fbdea1afc51c7c6ad26
 c51c │ c51ce410c124a10e0db5e4b97fc2af39
 ce2e │ 45c48cce2e2d7fbdea1afc51c7c6ad26
 d918 │ a87ff679a2f3e71d9181a67b7542122c
 e728 │ c81e728d9d4c2f636f067f89cc14862c
 fdf2 │ 70efdf2ec9b086079795c442636b55fb
(23 rows)

ROLLBACK

The dataset isn't very interesting, granted. You can speed that up with using the pg_trgm extension at https://www.postgresql.org/docs/current/static/pgtrgm.html

Upvotes: 1

klin
klin

Reputation: 121889

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);

Upvotes: 1

Related Questions