title: T-SQL string manipulation, replacement, comparing, pattern matching, regular expressions
body: <p>I have a short string of alphanumeric characters A-Z and 0-9</p>
  
  <p>Both Characters AND Numbers are included in the string.</p>
  
  <p>I want to strip spaces, and compare each string against a 'pattern' of which it will match only one. The Patterns use A to denote any character A-Z and 9 for any 0-9.</p>
  
  <p>The 6 patterns are:</p>
  
  <pre><code>A99AA
  A999AA
  A9A9AA
  AA99AA
  AA999AA
  AA9A9AA
  </code></pre>
  
  <p>I have these in a table with another column, with the correct space in place :-</p>
  
  <pre><code>pattern PatternTrimmed
  A9 9AA  A99AA
  A99 9AA A999AA
  A9A 9AA A9A9AA
  AA9 9AA AA99AA
  AA99 9AA    AA999AA
  AA9A 9AA    AA9A9AA
  </code></pre>
  
  <p>I am using SQL Server 2005, and I don't want to have 34 replace statements changing each of the characters and numbers to A's and 9's.</p>
  
  <p>Suggestions on how I can achieve this in a short succinct way, please.</p>
  
  <p>Here's what I want to avoid :-</p>
  
  <pre><code>update postcodes set Pattern = replace (Pattern, 'B', 'A') 
  update postcodes set Pattern = replace (Pattern, 'C', 'A') 
  update postcodes set Pattern = replace (Pattern, 'D', 'A') 
  update postcodes set Pattern = replace (Pattern, 'E', 'A')
  </code></pre>
  
  <p>etc.</p>
  
  <p>and</p>
  
  <pre><code>update postcodes set Pattern = replace (Pattern, '0', '9') 
  update postcodes set Pattern = replace (Pattern, '1', '9')
  update postcodes set Pattern = replace (Pattern, '2', '9')
  </code></pre>
  
  <p>etc</p>
  
  <p>Basically, I am trying to take a UK postcode typed in at a call centre by an imbecile, and pattern match the entered postcode against one of the 6 above patterns, and work out where to insert the space.</p>