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

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: ...

CodePudding user response：

you can use pg_trgm extension of postgresql.

pg_trgm- here you can easily find the similarity function <->.

you can use it like

text1 <-> text2

CodePudding user response：

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.