StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearching for phone numbers in mysql
text
Body
copied!<p>I have a table which is full of arbitrarily formatted phone numbers, like this</p> <pre><code>027 123 5644 021 393-5593 (07) 123 456 042123456 </code></pre> <p>I need to search for a phone number in a similarly arbitrary format ( e.g. <code>07123456</code> should find the entry <code>(07) 123 456</code></p> <p>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)</p> <pre><code>digits_only = lambda{ |n| n.gsub /[^\d]/, '' } needle = digits_only[input_phone_number] haystack.map(&digits_only).include?(needle) </code></pre> <p>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. </p> <p>Currently I can think of 2 'solutions'</p> <ul> <li>Hack together a franken-query of <code>CONCAT</code> and <code>SUBSTR</code></li> <li>Insert a <code>%</code> between every character of the needle ( so it's like this: <code>%0%7%1%2%3%4%5%6%</code> ) </li> </ul> <p>However, neither of these seem like particularly elegant solutions.<br> Hopefully someone can help or I might be forced to use the %%%%%% solution</p> <h3>Update: This is operating over a relatively fixed set of data, with maybe a few hundred rows. I just didn't want to do something ridiculously bad that future programmers would cry over.</h3> <p>If the dataset grows I'll take the 'phoneStripped' approach. Thanks for all the feedback!</p> <hr> <blockquote> <p>could you use a "replace" function to strip out any instances of "(", "-" and " ", </p> </blockquote> <p>I'm not concerned about the result being numeric. The main characters I need to consider are <code>+</code>, <code>-</code>, <code>(</code>, <code>)</code> and <code>space</code> So would that solution look like this?</p> <pre><code>SELECT * FROM people WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phonenumber, '('),')'),'-'),' '),'+') LIKE '123456' </code></pre> <p>Wouldn't that be terribly slow?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload