title: Is it possible to GROUP BY treating certain characters the same?
body: <p>I have tables with data as such:</p>
  
  <pre><code>forename        surname
  John            O'Neil
  Aaron           O Neil
  Peter           O-Neil
  Mary            Tollmache-Tollmache
  Beatrice        Tollmache Tollmache
  </code></pre>
  
  <p>All the data has been sanatised so they only contain characters from the Latin and Latin supplement character tables, as well as space ( ), appostrophe (') and hyphen (-).</p>
  
  <p>Is it possible to treat the characters space ( ), appostrophe (') and hyphen (-) the same when performing a GROUP BY.</p>
  
  <p>So, using the above table as an example, the following query:</p>
  
  <pre><code>SELECT surname, COUNT(*) AS cnt FROM tbl GROUP BY surname;
  </code></pre>
  
  <p>Would return:</p>
  
  <pre><code>surname                cnt
  O'Neil                 3
  Tollmache-Tollmache    2
  </code></pre>
  
  <p><strong>UPDATE</strong>: Jim's suggestion is a useful one if you are, as I am, using this query to build other tables, but probably would not be a good idea for deployment.</p>
  
  <p>Using Jim's suggestion, I ran the following tests on tables with the surname column indexed:</p>
  
  <pre><code>550,000 row table: 4.98 seconds
  3.375 million row table: 27.62 seconds
  12.485 million row table: 1 min 33 seconds
  </code></pre>