The string from the Breakfas column is in the Lunch column-CodePudding

I need to put in the result field the following value: true / false, depending on whether in the "Lunch" column there is a keyword that can be found in the "Breakfast" column. The expected result is:

enter image description here

WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad' UNION ALL
  SELECT 'Tomato pasta', 'Tomato soup' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup')
SELECT *,

***Field for your code*** result,

FROM Recipes;

Unfortunately the function: contains_substr (Breakfast, 'Tomato pasta') only takes into account one given string, and not all of the strings in the whole "Lunch" column.

Does anyone know how to solve this?

CodePudding user response：

Use below

select *,
  ( select count(*) > 0
    from unnest(split(Breakfast, ' ')) word1
    join unnest(split(Lunch, ' ')) word2
    on lower(trim(word1)) = lower(trim(word2))
  ) result
from Recipes

if applied to sample data in your question - output is