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.
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