Use regexp in mysql with where clause in Rails

Question

I am trying to use regexp to find some results through where clause. But results are coming as expected. Any help will be really appreciated.

username is asha123

Database query

User.where('username  REGEXP ?', /(asha|asha)/)

Result

User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE (username  REGEXP '--- !ruby/regexp /(asha|asha)/\n...\n')

=> #<ActiveRecord::Relation []>

Joshua Pinter · Accepted Answer · 2019-08-18 15:10:17Z

3

User.where('username  REGEXP ?', '(asha|asha)')

Although your database is expecting a "regular expression", ActiveRecord isn't - it just wants to replace your ? with a string value.

Update: to use a variable: just use regular variable interpolation within double quotes. Let's say you had first_name and last_name variables:

User.where('username  REGEXP ?', "(#{first_name}|#{last_name})")

Joshua Pinter

47.9k23 gold badges261 silver badges258 bronze badges

answered Mar 22, 2016 at 11:21

joshua.paling

14k4 gold badges47 silver badges61 bronze badges

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

2 Comments

Hey thanks Joshua it works :) How can I give variable in same thing like if I have asha in some variable ? How can I pass it then ?

No problem. Consider marking the answer as correct.