How to check if any string in array contains any other string (substring) in other array? (PostgreSQL)

I’ve got 2 string arrays, and I wanna to compare if any string of first array – contains SUBstring from other one, which storing these SUBstrings.

In other words: I don’t wanna compare these arrays and their strings fully, I need to find exactly if strings from one array contains a SUBstrings from other array.

Answer

You can use the following approach, if the two arrays are not too big:

SELECT * FROM
    (SELECT unnest('{apple pie,sugar,brown sugar}'::varchar[]) a1) t1,
    (SELECT unnest('{pie,sugar}'::varchar[]) a2) t2
    where t1.a1 like ('%' || t2.a2 || '%')

Otherwise I suggest you to use ts_vectors and the full text search operators: https://www.postgresql.org/docs/current/textsearch.html