Skip to content

Instantly share code, notes, and snippets.

@pboling
Last active Aug 29, 2015
Embed
What would you like to do?
Find Phone Numbers in Dirty Database
# SQL_PHONE_NORMALIZER Example:
# Lead.find_by_sql(PhoneFinder::SQL_PHONE_NORMALIZER.call(table: 'leads', column_name: 'phone', phone: '(555) 760-2012'))
# User.find_by_sql(PhoneFinder::SQL_PHONE_NORMALIZER.call(table: 'users', column_name: 'phone', phone: '555-223-4027'))
#
# AREL_PHONE_NORMALIZER Example:
# PhoneFinder::AREL_PHONE_NORMALIZER.call(rel: Lead.where(email: 'peter.boling@example.org'), table: 'leads', column_name: 'phone', phone: '(555) 760-2012')
# PhoneFinder::AREL_PHONE_NORMALIZER.call(rel: User.where(email: 'peter.boling@example.org'), table: 'users', column_name: 'phone', phone: '555-223-4027')
#
# See: https://coderbits.com/posts/pCl7og
module PhoneFinder
def self.phone_variations(phone)
phone_just_numbers = phone.gsub(/[^0-9]/, '')
phone_with_dashes = ActiveRecord::Base.sanitize(ActionController::Base.helpers.number_to_phone(phone_just_numbers))
phone_with_area = ActiveRecord::Base.sanitize(ActionController::Base.helpers.number_to_phone(phone_just_numbers, area_code: true))
phone_just_numbers = ActiveRecord::Base.sanitize(phone_just_numbers)
return phone_just_numbers, phone_with_dashes, phone_with_area
end
def self.where_clause(table:, column_name:, phone:)
phone_just_numbers, phone_with_dashes, phone_with_area = PhoneFinder.phone_variations(phone)
<<eos
( substring("#{table}"."#{column_name}" from '.?[[:digit:]]{3}.{0,2}[[:digit:]]{3}-?[[:digit:]]{4}') )
IN (#{phone_just_numbers},#{phone_with_dashes},#{phone_with_area})
eos
end
SQL_PHONE_NORMALIZER = -> (table:, column_name:, phone:) {
sql = <<eos
SELECT * from #{table}
WHERE #{PhoneFinder.where_clause(table: table, column_name: column_name, phone: phone)}
eos
sql
}
AREL_PHONE_NORMALIZER = -> (rel:, table:, column_name:, phone:) {
rel.where(PhoneFinder.where_clause(table: table, column_name: column_name, phone: phone))
}
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment