Q: SQL - find records that are prefixes of my string I have a table with a column named prefix storing 'aaaa'. My application has a long string, eg. 'aaaabbbbccccdddd'. I want to select all rows in which a particular column is a prefix of 'aaaabbbbccccdddd'. The length of the prefix is variable.
How can I do it in Postgres?
I tried
SELECT * FROM my_table WHERE '%' || prefix || '%' ILIKE 'aaaabbbbccccdddd'

but it does not match.

A: First, your way of using [I]LIKE is wrong:

string LIKE pattern 


So, string first, pattern second.
Furthermore, as you are after prefixes only, the starting wildcard is not needed.  Something like this should work:
SELECT * FROM my_table WHERE 'aaaabbbbccccdddd' ILIKE prefix || '%';

A small proof of this:
WITH my_table (prefix) AS (
VALUES ('abcde'),
       ('abcd'),
       ('abcE'),
       ('bcde')
)
SELECT * FROM my_table WHERE 'abcdefghijkl' ILIKE prefix || '%';

 prefix 
────────
 abcde
 abcd


A: Another option would be to create all the prefixes of the selected word and test them with equality =, (or ILIKE*), against the strings in the table:
select t.prefix 
from 
  (select 'aaaabbbbccccdddd'::text as word) as w,
  generate_series(0, length(w.word)) as g,
  lateral 
  (select t.prefix 
   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.
