I have a table which is full of arbitrarily formatted phone numbers, like this
027 123 5644
021 393-5593
(07) 123 456
042123456
I need to search for a phone number in a similarly arbitrary format ( e.g. 07123456
should find the entry (07) 123 456
The way I'd do this in a normal programming language is to strip all the non-digit characters out of the 'needle', then go through each number in the haystack, strip all non-digit characters out of it, then compare against the needle, eg (in ruby)
digits_only = lambda{ |n| n.gsub /[^\d]/, '' }
needle = digits_only[input_phone_number]
haystack.map(&digits_only).include?(needle)
The catch is, I need to do this in MySQL. It has a host of string functions, none of which really seem to do what I want.
Currently I can think of 2 'solutions'
CONCAT
and SUBSTR
%
between every character of the needle ( so it's like this: %0%7%1%2%3%4%5%6%
)However, neither of these seem like particularly elegant solutions.
Hopefully someone can help or I might be forced to use the %%%%%% solution
If the dataset grows I'll take the 'phoneStripped' approach. Thanks for all the feedback!
could you use a "replace" function to strip out any instances of "(", "-" and " ",
I'm not concerned about the result being numeric.
The main characters I need to consider are +
, -
, (
, )
and space
So would that solution look like this?
SELECT * FROM people
WHERE
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+')
LIKE '123456'
Wouldn't that be terribly slow?
A phone number should always be stored as a string or text and never an integer. Some phone numbers generally use hyphens and possibly parentheses. Also, you might need to indicate the country code before the phone number such as +46 5555-555555.
Inputs numbers should be 10 digits return message will be 'Valid No' When the mobile number starts with 9,8,7,6 - retrun Message 'Valid No' When the mobile number starts with 0,1,2,3,4,5 - return Message 'Invalid No Starts from 0-5' When mobile number = 000000000 - return message 'Invalid No'
The first command you will need to use is the SELECT FROM MySQL statement that has the following syntax: SELECT * FROM table_name; This is a basic MySQL query which will tell the script to select all the records from the table_name table.
This looks like a problem from the start. Any kind of searching you do will require a table scan and we all know that's bad.
How about adding a column with a hash of the current phone numbers after stripping out all formatting characters. Then you can at least index the hash values and avoid a full blown table scan.
Or is the amount of data small and not expected to grow much? Then maybe just sucking all the numbers into the client and running a search there.
I know this is ancient history, but I found it while looking for a similar solution.
A simple REGEXP may work:
select * from phone_table where phone1 REGEXP "07[^0-9]*123[^0-9]*456"
This would match the phonenumber
column with or without any separating characters.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With