Search code examples
postgresqlsqlitepattern-matchingsql-like

PostgreSQL like not returning matching instances

How does the PostrgeSQL like function work? I'm using token inputs to limit input from user with only existing values.

I have the following values in the DB:

  `Yellow, White, Orange...`

My Code

 @colors = Color.where("name like ?", "%#{params[:q]}%")

If I type in w for example White is not proposed. I have to type in second letter to see White proposed. Because Db values all start by Capital letter I suspect a difference with SQLite.

I found this post which mentions ILIKE but was wondering if there is some common code that work both with Postgres and SQLite.

Solution

  • The case-sensitivity of LIKE depends on the database you use. Some databases ignore case when using LIKE, some don't, some look at various configuration options. One way around this is to normalize the case yourself by converting to upper or lower case:

    @colors = Color.where("lower(name) like ?", "%#{params[:q].downcase}%")