Postgres string matching in WHERE between two fields

match.sql

CREATE TABLE pattern_test (field1 text, field2 text);
INSERT INTO pattern_test VALUES ('abc', '123 abc 546'), ('def', '123def546'), ('xyz', 'xyz something'), ('abo', 'something about something');

-- We want to find all records where field2 contains field1 as a distinct string (i.e. get records 0 and 2)
-- ~* is similar to ILIKE, but accepts a regular expression
-- '\y' will make sure that field1 is always a distinct object in field2

SELECT * FROM pattern_test WHERE field2 ~* concat('\y',field1,'\y');