CODEWITHSUNDEEP
mysqlruby-on-railsfindcollationcase-sensitive
klew
klew

Reputation: 14967

Case sensitive find in Rails

I'm using Mysql with collation utf8_general_ci and for most of my searches it is good. But for one model and one field I want to find a record with case sensitive. How to do it?

Upvotes: 2

Views: 2500

Answers (2)

Juha Syrjälä
Juha Syrjälä

Reputation: 34271

It is MySQL that is doing the case insensitive query, not Ruby on Rails.

See http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

You could make database columns, that require case sensitivity to be case sensitive

  1. Modify fields to be BINARY or VARBINARY instead of CHAR and VARCHAR.
  2. Modify fields to have binary collation (e.g. latin1_bin)

-

 create table tbl_name (
    ...
    data varchar COLLATE latin1_bin
 )

Or you can modify your queries to use COLLATE operator:

SELECT * from tbl_name WHERE col_name COLLATE latin1_bin LIKE 'a%'

Upvotes: 3

Roland Bouman
Roland Bouman

Reputation: 31961

If you always want to search that column in a case sensitive manner, the best thing would be to define it with collation utf8_bin

Upvotes: 0

Related Questions