Find rows that have a field which is a substring of the search string

Question

Given a table like so:

id      |    value
-------------------
1       |     food
2       |     foot
3       |     barfoo
4       |     bar
5       |     baz

Using postgres I want to find all of the rows where the value field matches from the start of the search field. Sort of like SELECT * FROM table where 'foo' ilike value%

Searching for 'foo' would return food and foot but not barfoo.

I think this should be easy but I'm missing something obvious.

Answer 1

You have the arguments to ILIKE the wrong way round:

SELECT * FROM table where value ilike 'foo%'

Answer 2

shouldn't the comparison be switched

where value ilike 'foo%'

Edit

• Changed to Case Insensitive "ilike", per original example.

So many SQL dialects, so little greymatter storage space.