SQL - find records that are prefixes of my string

Question

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.

Answer 1

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

Answer 2

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.