Searching for phone numbers in mysql

I have a table which contains randomly formatted contact number, similar to this

027 123 5644
021 393-5593
(07) 123 456

I require to look for a phone number in an in a similar way approximate layout (as an example 07123456 need to locate the access (07) 123 456

The means I would certainly do this in a regular shows language is to remove all the non - figure personalities out of the 'needle', after that experience each number in the haystack, strip all non - figure personalities from it, after that contrast versus the needle, eg (in ruby)

digits_only = lambda{ |n| n.gsub /[^\d]/, '' }

needle = digits_only[input_phone_number]

The catch is, I require to do this in MySQL. It has a host of string features, none of which actually appear to do what I desire.

Presently I can consider 2 'remedies'

  • Hack with each other a franken - question of CONCAT and also SUBSTR
  • Insert a % in between every personality of the needle (so it resembles this: %0%7%1%2%3%4%5%6%)

However, neither of these feel like specifically classy solutions.ยข Hopefully a person can aid or I could be compelled to make use of the %%%%%% remedy

Update: This is running over a reasonably dealt with set of information, with possibly a couple of hundred rows. I simply really did not intend to do something extremely negative that future designers would certainly sob over.

If the dataset expands I'll take the 'phoneStripped' strategy. Many thanks for all the responses!

can you make use of a "replace" function to remove out any kind of circumstances of " (", " - " and also " ",

I'm not worried concerning the outcome being numerical. The major personalities I require to take into consideration are +, -, (, ) and also space So would certainly that remedy resemble this?

SELECT * FROM people 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+')
LIKE '123456'

Wouldn't that be horribly reduce?

2022-06-07 14:37:26
Source Share
Answers: 9

My remedy would certainly be something along the lines of what John Dyer claimed. I would certainly add a 2nd column (as an example phoneStripped) that obtains removed on insert and also upgrade. Index this column and also search on it (after removing your search term, certainly).

You can additionally add a trigger to instantly upgrade the column, although I've not collaborated with triggers. Yet like you claimed, it is actually hard to write the MySQL code to remove the strings, so it is possibly less complicated to simply do it in your customer code.

(I recognize this is late, yet I simply began checking out below

2022-07-02 20:27:04

if this is something that is mosting likely to take place often probably changing the information to be all one layout and afterwards arrangement the search kind to remove out any kind of non - alphanumeric (if you permit numbers like 310 - BELL) would certainly be an excellent suggestion. Having information in a conveniently looked layout is half the fight.

2022-06-07 16:29:00

This resembles a trouble from the beginning. Any kind of sort of looking you do will certainly call for a table check and also most of us recognize that misbehaves.

Just how around including a column with a hash of the existing contact number after removing out all formatting personalities. After that you can at the very least index the hash values and also stay clear of a complete blown table check.

Or is the quantity of information tiny and also not anticipated to expand a lot? After that possibly simply drawing all the numbers right into the customer and also running a search there.

2022-06-07 16:27:32

An out - of - the - box suggestion, yet could you make use of a "replace" function to remove out any kind of circumstances of " (", " - " and also " ", and afterwards make use of an "isnumeric" function to examine whether the resulting string is a number?

After that you can do the very same to the phone number string you are looking for and also contrast them as integers.

Certainly, this will not benefit numbers like 1800 - MATT - ROCKS.

2022-06-07 15:04:50

Is it feasible to run a question to reformat the information to match a wanted layout and afterwards simply run a straightforward question? In this way also if the first reformatting is slow-moving you it does not actually issue.

2022-06-07 15:04:25

MySQL can search based upon normal expressions.

Certain, yet offered the approximate format, if my haystack had "(027) 123 456" (remember placement of rooms can transform, it could equally as conveniently be 027 12 3456 and also I intended to match it with 027123456, would certainly my regex consequently require to be this?


(in fact it would certainly be even worse as the mysql guidebook does not appear to show it sustains \D)

If that holds true, isn't it essentially the like my %%%%% suggestion?

2022-06-07 15:04:17

Just a suggestion, yet could not you make use of Regex to promptly remove out the personalities and afterwards contrast versus that like @Matt Hamilton recommended?

Perhaps even set up a sight (not exactly sure of mysql shown) that would certainly hold all contact number removed by regex to a simple phone number?

2022-06-07 15:04:07

Woe is me. I wound up doing this:

mre = mobile_number && ('%' + mobile_number.gsub(/\D/, '').scan(/./m).join('%'))

find(:first, :conditions => ['trim(mobile_phone) like ?', mre])
2022-06-07 15:03:42

This is a trouble with MySQL - the regex function can match, yet it can not change. See this post for a feasible remedy.

2022-06-07 15:02:41