MySQL REGEXP using Rails ActiveRecord

Question

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

Unfortunately it doesn't work.... [17] pry(main)> User.where("'full_name' LIKE ?", '% %').count => 0 — superuseroi
– superuseroi, Commented Mar 19, 2016 at 14:09

Paul A Jungwirth · Accepted Answer · 2016-03-19 14:11:35Z

7

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.

answered Mar 19, 2016 at 14:11

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL REGEXP using Rails ActiveRecord

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related