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

StackExchange Database Administrators Q#149630, answer score: 8