Using like operator produces case sensitive results

[serogers]

The documentation mentions that when using the LIKE operator, the search should be case insensitive. However, I cannot seem to get this to work. All my searches are case sensitive. Below is what I have done, am I missing something?

DATABASE

MySQL "content" attribute is a varchar

RAILS

Rails: 3.1, Ruby: 1.8.7

SCOPE

scoped_search :on => :content, :default_operator => :like

SEARCH

Post.search_for("Attempt")

GENERATED QUERY

SELECT posts.* FROM posts WHERE ((posts.content LIKE '%Attempt%'))

ACTUAL POST CONTENT

This is a test, attempt #3

Since the post contains the word "attempt" as a lower case word, this record isn't found when searching "Attempt". How can I make this case insensitive. Other than this issue, I am loving the gem! Thanks for your help.

[abenari]

Hi Spencer Rogers, Thanks for bringing the issue to our attention. Reading MySQL documentation here: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html I found the following: 'The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default.'

My guess is that it could be the collation used in your database. Thanks, Amos.

----- Original Message -----

From: "Spencer Rogers" reply@reply.github.com To: "abenari" abenari@redhat.com Sent: Monday, February 20, 2012 6:42:22 AM Subject: [scoped_search] Using like operator produces case sensitive results (#19)

The documentation mentions that when using the LIKE operator, the search should be case insensitive. However, I cannot seem to get this to work. All my searches are case sensitive. Below is what I have done, am I missing something?

DATABASE

MySQL "content" attribute is a varchar

RAILS

Rails: 3.1, Ruby: 1.8.7

SCOPE

scoped_search :on => :content, :default_operator => :like

SEARCH

Post.search_for("Attempt")

GENERATED QUERY

SELECT posts.* FROM posts WHERE ((posts.content LIKE '%Attempt%'))

ACTUAL POST CONTENT

This is a test, attempt #3

Since the post contains the word "attempt" as a lower case word, this record isn't found when searching "Attempt". How can I make this case insensitive. Other than this issue, I am loving the gem! Thanks for your help.

---

Reply to this email directly or view it on GitHub: https://github.com/wvanbergen/scoped_search/issues/19

[wvanbergen]

Also, utf8_general_ci is case insensitive as well.

This should really be fixed on your database's side, because trying to fix these issues in the application layer will make the code more complex than it needs to be and the speed of queries will tank.

[serogers]

Ah yes, that was the problem. That specific column was listed as utf8-bin. Thanks for the heads up.

[owenwiddicombe]

I need to be able to query an Oracle Database that is case sensitive. I would like the searches to be case-insensitive. I have no control over the external Oracle database and cannot change the collation. Presumably it is case sensitive for a reason. How can I use scoped_search to find case insensitive results?