title: Searching for phone numbers in mysql
body: <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(&amp;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 &quot;replace&quot; function to strip out any instances of &quot;(&quot;, &quot;-&quot; and &quot; &quot;,</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>