How do I check if the strings inside an array are part of another string?

I have a dynamic array of strings. I would like to check if some of the elements are equal to or sometimes also part of an input string.

I came up with this example:

SELECT array_contains(array("ghi", "def", "%abc"), "foobabc")

This, however, does not work and returns false.

Is there any other way to do this besides building a large clause with LIKE statements?

The array_contains function in your example checks if the input string "foobabc" is an exact match for any of the elements in the array ["ghi", "def", "%abc"]. Since there is no exact match, the function returns false. In order to check if array element has partial or exact match with the string "fooabc", you can follow the below approach.

Code:

SELECT  array("ghi", "def", "abc") as array_col,transform(array("ghi", "def", "abc"), value ->  contains("fooabc",value)) as contains_col, array_max(transform(array("ghi", "def", "abc"), value ->  contains("fooabc",value))) as array_contains_col

The transform function creates a new array that contains a boolean value for each element in the original array. The contains function within transform is used to check if the input string "fooabc" contains exactly or partially each element in the array. The resulting array contains_col will contain [false, false, true], since "abc" is the only element in the array that is a substring of "fooabc". The array_max function is then used to find the maximum value in the contains_col array, which is true. The result of the array_max function is a scalar value, which is assigned to the alias array_contains_col.

This is the approach to check if an input string contains any of the elements in the array.