CODEWITHSUNDEEP
sqlpostgresql
dsas
dsas

Reputation: 1670

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

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.

Upvotes: 7

Views: 5100

Answers (2)

Rawheiser
Rawheiser

Reputation: 1228

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.

Upvotes: 6

Mark Byers
Mark Byers

Reputation: 839164

You have the arguments to ILIKE the wrong way round:

SELECT * FROM table where value ilike 'foo%'

Upvotes: 0

Related Questions