CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
Giuseppe
Giuseppe

Reputation: 5348

Case-sensitive database search with Active Record

Does Active Record provide a way to generate SQL that forces a text search to be case-sensitive?

Ruby-on-Rails generators instructed to create a string-type column produce a simple VARCHAR(255) field, in a mysql database. It turns out that queries on such columns are case insensitive by default.

Thus, an Active Record search such as:

Secret.where(token: 'abcde')

will match records with tokens abcde, ABcdE, etc.

Without changing the underlying database column (e.g. specifying a utf8_bin collation) searches can be made case sensitive by explicitly tweaking the where clause:

Secret.where('binary token = ?', 'abcde')

However, this is database-specific, and I am wondering if Active Record has an idiom to accomplish the same for any database. Just as an example, something resembling the where.not construct:

Secret.where.binary(token: 'abcde')

Wouldn't this be a common enough need?

Upvotes: 3

Views: 1834

Answers (1)

Pavel Mikhailyuk
Pavel Mikhailyuk

Reputation: 2877

In short: there is NO ActiveRecord idiom for case-sensitive search.

For case-insensitive search you can try to use this. It still works, but source code was changed a bit. So, use it on your own risk.

In general, case sensitivity is subset of the Collation idiom. And different DBMS use very different default collations for string(text) data types, including default case sensitivity. Detailed description for MySQL.

There is a sql operator COLLATE which is very common across DBMS(but seems still is not in SQL Standard). But ActiveRecord sources show it only in schema creation code. Neither ActiveRecord, nor Arel gems do not use COLLATE in where search(sad).

Note: Please, don't miss the database tag(mysql etc) in a Question. There are many Questions and Answers on SO without the tags(or with sql one), which are completely irrelevant for the most of DBMSs except author's one.

Upvotes: 1

Related Questions