Search code examples
sqlstringpostgresqlmatchwhere-clause

How to match a substring between two record values in PosgreSQL?

I am looking for a solution to match two hidden strings in PostgreSQL. One record's value is a substring, the second is a text to match with. The code idea looks like this:

SELECT text_1, text_2
FROM texts
WHERE text_1 SIMILAR TO text_2;

The preferred output would be like this:

text_1, text_2
______________
foo_, foo_bar
sta, stack
love, ilove2code

The best analogue of Python:

if text_1 in text_2: ...
Solution

  • The easiest way to get behavior the most similar to python's in syntax is to use strpos:

    SELECT text_1, text_2
FROM texts
WHERE strpos(text_2, text_1) > 0;

    The problem with using text_2 like '%' || text_1 || '%' or text_2 ~ text_1 or similar pattern matching is that you would have to ensure that your patterns don't contain any special characters that would influence the matching.

    Here's a fiddle that demonstrates the solution and the problems with the others.