CODEWITHSUNDEEP
sqlpostgresql
burger
burger

Reputation: 5883

PostgreSQL join using LIKE/ILIKE

I am trying to do an inexact join (I am not sure what the proper term is) where I could perform pattern matching. Basically, instead of doing a JOIN like this:

.... JOIN .... ON (t1.col = t2.col)

I would like to do do something like:

.... JOIN .... ON (t1.col ILIKE %(t2.col)% )

The second example is obviously not proper syntax. Is there a way to do something like that?

Upvotes: 20

Views: 37033

Answers (2)

bobince
bobince

Reputation: 536587

An alternative way to join on “is the value of t2.col a substring of t1.col”:

... AS t1 JOIN ... AS t2 ON POSITION(t2.col IN t1.col)<>0

This still can't use indexes, but the advantage is you don't have to worry about % and _ characters in t2.col which would otherwise start matching everything.

If you need case-insensitive matching like ILIKE and you're not using citext you'd need to LOWER() both columns before using POSITION().

Upvotes: 32

mechanical_meat
mechanical_meat

Reputation: 169444

.... JOIN .... ON t1.col ILIKE '%' || t2.col || '%'

Please note that as written, AFAIK, PostgreSQL won't be able to use any indexes to speed up the join processing.

Upvotes: 47

Related Questions