Postgresql - Check if a given string starts with any element of the array of strings

Question

Given two strings we can do:

select 'aaa123' ilike 'aaa'||'%'

The result will be TRUE. I would like to do the same thing with a string and an array - if the given string starts with any of the elements of the array of strings than the result would show TRUE.

For example (array and string):

select array['aaa123'::text,'bbb123'::text] as text_array
select 'aaa12345' as string

I would like to do something like this:

select string ilike ANY(text_array || '%')

And I would expect TRUE since aaa12345 starts with aaa123 (element of the array).

Thanks a lot for the help!

Answer 1

You could unnest() the array of strings and then compare your input string with every element like you wanted.

You would get as many rows in the output as there are elements in your array. Since you need a clear indicator whether any of the comparison against array element yields true use bool_or() aggregate function:

select 
  bool_or('string12345' ilike arr_element||'%') 
from 
  unnest(ARRAY['string123','something']::text[]) x(arr_element);

This would give you TRUE since:

SELECT 'string12345' ilike 'string123%' -- true

Note: bool_or() returns true if at least one input value is true, otherwise false.

Answer 2

select string ilike ANY(
    select s || '%'
    from unnest(text_array) s(s)
    )