How to use ilike for phone number filter-CodePudding

Hi im fetching the user input and displaying the records that matches the condition, my query will look like

customers = customers.where('customers.contact_num ilike :search', {search: "%#{options[:search_contact]}%"})

here in db the contact number is stored in string with the format (091)-234-5678 like that

on while searching the user on basis of contact number if i search like this

091 it filters the number correctly, but while searching like 0912, it doesn't display record due to the braces, so how to modify the query to neglect the ) and - while searching.. As im new to the domain please help me out

thanks in advance

CodePudding user response：

Changing the query is hard. Let's not do that.

Instead right a quick script to transforms your numbers into 1112223333 form. No formatting at all. Something like:

require 'set';
phone = "(234)-333-2323"
numbers = Set.new(["1","2","3","4","5","6","7","8","9","0"])
output = phone.chars().select{|n| numbers.include?(n)}.join("")
puts output
=> "2343332323"

Then write a little function to transform them into display form for use in the views.

This will make your query work as is.

CodePudding user response：

What about using REGEXP_REPLACE to remove all non-digit chars from the search - something like below?

customers = customers.where("REGEXP_REPLACE(customers.contact_num,'[^[:digit:]]','','g') ilike :search", {search: "%#{options[:search_contact]}%"})