Another option would be to create all the prefixes of the selected word and test them with equality
ILIKE*), against the strings in the table:
(select 'aaaabbbbccccdddd'::text as word) as w,
generate_series(0, length(w.word)) as g,
from my_table as t
where t.prefix = left(w.word,g)
-- where t.prefix ilike left(w.word,g)
) as t ;
This might be more efficient in big tables, doing only a few (as many as the length of word, +1) equality checks using index seeks and not performing a full index or table scan.
*: If you need case insensitive check, then
ILIKE should be used as the commented code above. The improvement in this case from the
'abcdefghijkl' ILIKE prefix || '%' answer is that the
prefix column is used on the left of
ILIKE and not on the right side. So, the query will be performing a few
column ILIKE 'aaabbbccdd' checks and not (an arbitrary number of)
'aaabbbccdd' ILIKE column||'%' checks.
Both ways we have a number of different
ILIKE conditions but in one case that number is the length of the searching word and in the other the number of rows in the table.