title: How to search for matches with optional special characters in SQL?
body: <p>I have a problem with a search query I am using my data contains names and information that has apostrophes in various forms (HTML encoded and actual).
  So for example I would like an alternative to this:  </p>
  
  <pre><code>SELECT * FROM Customers WHERE REPLACE(LastName,'''','') 
  LIKE Replace('O''Brien,'''','')
  </code></pre>
  
  <p>This is just an example, what I want is a way where if someone types OBrien or O'Brien this will still work, I need to replace three versions of the character and the data is feed sourced and cannot be changed - what can be done to a query to allow for this kind of search to work.<br>
  I have Items with names which work this way which currently have many nested REPLACE functions and cannot seem to find something that will work this way, which is more efficient.<br>
  I am using MS SQL 2000 with ASP if that helps.</p>
  
  <hr>
  
  <p><strong>Edit</strong></p>
  
  <p>Here is the query that needs to match O'Brien or OBrien, this query does this but is too inefficient - it is joined by another for Item Names and FirstName (optional) for matching.</p>
  
  <pre><code>SELECT * FROM Customers
  WHERE 
  REPLACE(REPLACE(REPLACE(LastName,'&amp;apos;',''),'&amp;#39;',''),'''','') 
  LIKE 
  REPLACE(REPLACE(REPLACE('%O&amp;#39;Brien%','&amp;apos;',''),'&amp;#39;',''),'''','')
  </code></pre>