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?
views:
385answers:
2
A:
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
Roland Bouman
2009-12-23 22:01:37
How can I do it?
klew
2009-12-23 22:04:36
+2
A:
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
- Modify fields to be BINARY or VARBINARY instead of CHAR and VARCHAR.
- 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%'
Juha Syrjälä
2009-12-23 22:01:58
Yes, I know, but I want to find it in rails with case sensitive when all other searches are case insensitive
klew
2009-12-23 22:05:28
I know how to do it in MySql. I was asking how to do it in Rails. I already did it with Model.find_by_sql but I'm looking for cleaner solution.
klew
2009-12-23 22:31:19
Basically either mysql uses datatypes for columns that have case sensitive property or SELECT query forces matching to be case sensitive. Ruby-on-rails does not affect the former and I do not know any other way to do the latter than using Model.find_by_sql
Juha Syrjälä
2009-12-25 10:05:16