CODEWITHSUNDEEP
mysqlruby-on-railsrubyregexactiverecord
superuseroi
superuseroi

Reputation: 1348

MySQL REGEXP using Rails ActiveRecord

This has been bugging me for a while now... I need to query a table of users and find those that have a double space in the name. I have multiple versions of SQL queries that work directly against the db, the simplest and more succinct being: 

SELECT 'first  last' REGEXP '[[:space:]]{2}';

I can also make it work from rails using ruby REGEX:

User.pluck(:full_name).select {|n| n =~ /([[:alpha:]]*)[[:space:]]{2}/ }

OR

User.pluck(:full_name).select {|n| n =~ /\w+\s{2}/ }

I have been trying to use AR where in several ways, but I am not sure what I am missing. Maybe I need to escape something... I read a few times: http://dev.mysql.com/doc/refman/5.0/en/regexp.html

I just don't see why it doesn't work

2] pry(main)> User.where("'full_name' REGEXP ?", "[[:alpha:]]*[[:space:]]{2}").count
=> 0
[3] pry(main)> User.where("'full_name' REGEXP ?", '[[:alpha:]]*[[:space:]]{2}').count
=> 0
[4] pry(main)> User.where("'full_name' REGEXP ?", '[[:alpha:]]*[[:space:]]{2}').to_sql
=> "SELECT `users`.* FROM `users` WHERE ('full_name' REGEXP '[[:alpha:]]*[[:space:]]{2}')"
[5] pry(main)> User.where("'full_name' REGEXP ?", '[[:space:]]{2}')
=> []
[6] pry(main)> User.where("'full_name' REGEXP ?", '[[:blank:]]{2}')
=> []
[7] pry(main)> User.where("'full_name' RLIKE ?", '[[:blank:]]{2}')
=> []
[8] pry(main)> User.where("'full_name' RLIKE ?", '[[:blank:]]{2}').to_sql
=> "SELECT `users`.* FROM `users` WHERE ('full_name' RLIKE '[[:blank:]]{2}')"
[9] pry(main)> User.where("'full_name' RLIKE ?", '[[:blank:]]').count
=> 0
[10] pry(main)> User.where("'full_name' RLIKE ?", '[[:space:]]').count
=> 0
[11] pry(main)> User.where("'full_name' RLIKE ?", '.*[[:space:]].*').count
=> 0
[12] pry(main)> User.where("'full_name' RLIKE ?", '\[[:space:]\]').count
=> 0

Upvotes: 3

Views: 2499

Answers (1)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

The problem is not in your regex but in your column name. You are asking MySQL if the literal string 'full_name' has two spaces. Change this:

User.where("'full_name' REGEXP ?", '[[:space:]]{2}')

to this:

User.where("full_name REGEXP ?", '[[:space:]]{2}')

After you do that I think most/all of your attempts will work.

Upvotes: 7

Related Questions