CODEWITHSUNDEEP
postgresqlsqlitepattern-matchingsql-like
Gaelle
Gaelle

Reputation: 599

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.

Upvotes: 2

Views: 2743

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656301

The SQLite LIKE operator is case insensitive per default.
In PostgreSQL ILIKE is the case insensitive version of LIKE. There are also operators:

~~   .. LIKE
~~*  .. ILIKE
!~~  .. NOT LIKE
!~~* .. NOT ILIKE

These three expressions are all effectively the same in PostgreSQL:

name ilike '%w%'
name ~~*   '%w%'
lower(name) like lower('%w%')

The last line mostly works in both SQLite and PostgreSQL. I added links to the respective manual pages.

A limitation applies: SQLite only understands lower / upper case of ASCII characters, while PostgreSQL understands other UTF-8 characters, too.

Upvotes: 2

mu is too short
mu is too short

Reputation: 434585

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}%")

Upvotes: 0

Related Questions