snowflake – Search a text column for a string from a list, and return the found string

I’m trying to search larger text for one of multiple values, lets say “Jack”, “Jill”, or “Bob” (the words can be 50 characters long).

With Snowflake I have something like

select count(*)
from my_table
where text_field like any ('%jack%', '%Jill%', %bob%')

That’s roughly it. Now how do I indicate if the field contains 'jack', 'Jill', or 'bob' specifically? I need to EXTRACT the found text. Also now imagine that instead of 3 values, there are a 100 values to search, so preferably not a solution where manual typing is involved like charindex('jack') or something like it.

I can store the values to search (100 words) in an another table. Let’s call it my_dictionary with one column my_word.

How can I accomplish this?

Read more here: Source link