Rails & Oracle - Case Sensitivity
Posted by Stijn Pint on Jul 26, 2007
I ran into a small problem today while making an ajax based filter for a table. (Kinda like this example: http://dev.nozav.org/ajaxtable )
I want this query to be case IN-sensitive ofcourse, but in Oracle this is apparently not so by default.
A quick google gave me a first possible solution: databasejournal.com.
In Oracle 10g, the function "REGEXP_LIKE" is introduced, which can be used like this for case insensitive matching:
REGEXP_LIKE(fieldname, 'your_query', 'i')
No such luck however, it seems the ruby OCI adapter (http://ruby-oci8.rubyforge.org/en/) doesn't support this (yet).
My solution: Transform everything to uppercase, both your query and the DB-values:
Model.find(:all,:conditions=>["upper(field) like ?", "%#{param[:query].upcase}%"])
Dirty, but it seems to work fine... If you have a better solution for this, please share it !