title: MySQL query to match UK postcodes regardless of the number of spaces
body: <p>I have the simplest table in the world, for looking up lat/lng values for a UK postcode (loaded with full UK postcode data):</p>
  
  <pre><code>CREATE TABLE postcodes (
    postcode char(7) NOT NULL,
    lat double(10,6) NOT NULL,
    lng double(10,6) NOT NULL,
    KEY postcode (postcode)
  )
  </code></pre>
  
  <p>Postcodes in 'postcode' field either have 2 digits at the end of the first half, or one and then a space. I think the space is important for the integrity of how they are matched (??), and besides I don't want to remove the spaces in the table, as I'm also pulling out the postcodes for display purposes (and I don't want a duplicate field, because I'm fussy!). Examples:</p>
  
  <pre><code>'LE115AF', 'BS6 5EE', 'W1A 1AA', 'BS216RS', 'M3 1NH'
  </code></pre>
  
  <p>So, some have spaces, some don't. Most are 7 chars overall, some only 6.</p>
  
  <p>Anyway, point being is I want users to be able to enter postcode queries, including partial postcodes, with or without spaces, and always find a match if their input string is valid (i.e. they don't enter a full or partial postcode which doesn't exist in the table).</p>
  
  <p>This is how I've done it so far (with some help from PHP):</p>
  
  <pre><code>{...} WHERE `postcode` LIKE '" . str_replace(' ','%',$query) . "%' LIMIT 1
  </code></pre>
  
  <p>This is good for:</p>
  
  <ul>
  <li>full postcodes which don't contain spaces in the db</li>
  <li>partial postcodes if a space has been entered and there is a corresponding space in the db, or the queried portion stops short of where the space occurs (e.g. 'W1A' will match 'W1A 1AA', 'M3 1' will match 'M3 1AR', etc).</li>
  </ul>
  
  <p>But doesn't work for these queries:</p>
  
  <ul>
  <li>'W1A1AA' should match 'W1A 1AA'</li>
  <li>'BS65EE' should match 'BS6 5EE'</li>
  <li>'BS65' should match the first 'BS6 5%' postcode in db, which is 'BS6 5AA'</li>
  <li>'M31' should likewise match 'M3 1AR'</li>
  </ul>
  
  <p>I'm guessing I need to somehow do some MySQL string function magic to work out if there's a space in the row's postcode field, and adjust my WHERE clause logic accordingly? Anyone got any advice on the best approach? I ideally want to also:</p>
  
  <ul>
  <li>avoid MySQL stored procedures (inline functions preferred)</li>
  <li>do nothing more than inline string functions in the PHP part too</li>
  </ul>