title: MySQL query to match on multiple patterns
body: <p>I'm trying to figure out how to approach a solution where I can query a table that has a field with multiple formats, and my input format may vary as well.</p>
  
  <p>I have a few tables that have the same PIN column (VARCHAR(20)), but in each of the tables, the format can vary like shown below. Typically it's one format per table, but you can see all the different variations I might run into.</p>
  
  <pre><code>PIN               |  ID
  ---------------------------
  01-123.040-111-2  |  5
  01-123.04-111     |  6
  003.242424242.23  |  7
  01.1234.345.22    |  8
  1234456789        |  9
  </code></pre>
  
  <p>I'd like to be able to accept any of the following variations of input below:</p>
  
  <pre><code>&gt; 012304041112
  &gt; 01.3456.342.22
  &gt; 02-3232323.2331
  </code></pre>
  
  <p>Maybe some of the input formats will exactly match, some wont. So here's what I'm thinking:</p>
  
  <p>I'm using PHP, so I can strip out the -'s and .'s or any spaces to just get the raw number, but I don't know how to make a comparison to that number that might be in the column in the table. If there is a way of comparing digits to just digits that would most likely be ideal.</p>
  
  <p>For example:</p>
  
  <pre><code>input of 647382627 would match on 64.738.262-7 in the database
  </code></pre>
  
  <p>Another situation might be where there is input like this:</p>
  
  <blockquote>
    <p>12-25-9-123</p>
  </blockquote>
  
  <p>Where it should match:</p>
  
  <pre><code>12-25-009-123
  </code></pre>
  
  <p>[edit] To Clarify what I mean here-
  Different counties use different patterns for parcel numbers. A county might use:</p>
  
  <pre><code>XX-XXXX-XXX-XX
  </code></pre>
  
  <p>for their pattern, but in some documents they might use say:</p>
  
  <pre><code>10-1234-5-2 where it translates to 10-1234-005-02
  </code></pre>
  
  <p>We'd know what counties this applies to, but the input may be</p>
  
  <pre><code>10123452 or 10-1234-005-02 or 10-1234-5-2
  </code></pre>
  
  <p>So I don't know how to exactly make that comparison. I guess if you'd strip dashes and zeros from input and the column you could come close, and just return a few matches to pick from if need be.</p>