CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby-on-rails-3
Huy
Huy

Reputation: 11206

Stripping Phone Numbers of Non-numeric Values During SQL Query in Rails

I have a list of new restaurants and I want to check them against a database of restaurants using the phone number. The problem with phone numbers is that they have different formatting in the database (i.e. 123-345-6789 or (123)-456 8988).

So far, I have created an index for the phone so that I search through them quickly. I also stripped the phone number of the new restaurants so that they only contain numeric values (0-9).

Now, I want to strip the databases' numbers right before I compare so that I will strictly comparing numbers.

#strip the phone of non-numeric values and spaces
formatted_phone = SunspotHelper.sanitize_term(pr.phone).gsub(/\s+/, "") 

Restaurant.where(contact_phone: formatted_phone).each do |r|
  #irrelevant code here
end

The problem with the above code is that contact_phone (from database) will be in its non-stripped format. My research so far has lead me to believe that I have to use something like REGEXP_LIKE, but I'm not quite sure how to implement this with Rails. I do NOT want to update the database - I simply want to strip the numbers for comparison while still keeping the original formatting in the DB.

How do I strip the phone numbers, contact_phone, of non-numeric values from the database before I compare it with formatted_phone?

Upvotes: 0

Views: 562

Answers (1)

tamersalama
tamersalama

Reputation: 4143

If you'd just like to limit your effort to the Rails query (instead of properly formatting the phone numbers in another field in the database) here's how you could use your DB engine's REGEXP capabilities

Restaurant.where(["REGEXP_REPLACE(contact_phone, '[^[:digit:]]', '') = ?", formatted_phone]).each do |r|
  #irrelevant code here
end

Upvotes: 1

Related Questions