title: sql date format patindex regex replace recall
body: <p>Using SQL, is there an elegant way to remove the 'th' 'rd' and 'st' after a date?  Here is how I'm doing it, but I'd rather do something like regex recall (example below).  </p>
  
  <p>Please no functions with while loops.  I've already seen those solutions and I'm looking for something less ugly.</p>
  
  <pre><code>select Replace('september 8th, 2016', Substring('september 8th, 2016', PatIndex('%[a-z,A-Z][a-z,A-Z],%', 'september 8th, 2016'), 2), '')  statdate2
  </code></pre>
  
  <p>if using recall I could do something like below</p>
  
  <pre><code>/(\s[0-9]+)[a-z,A-Z]{2}\,/
  </code></pre>
  
  <p>and recall what the date number is with the replacement</p>
  
  <pre><code> $1,
  </code></pre>
  
  <p>As-is my pattern inthe sql example may pull in any two characters followed by a comma which is good 99% of the time, but does match incorrectly some unwanted date misformats where users put the comma after the month.</p>