How to search for matches with optional special characters in SQL?

Question

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:

SELECT * FROM Customers WHERE REPLACE(LastName,'''','') 
LIKE Replace('O''Brien,'''','')

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.
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.
I am using MS SQL 2000 with ASP if that helps.

---

Edit

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.

SELECT * FROM Customers
WHERE 
REPLACE(REPLACE(REPLACE(LastName,''',''),''',''),'''','') 
LIKE 
REPLACE(REPLACE(REPLACE('%O'Brien%',''',''),''',''),'''','')

Answer 1

You could try this:

SELECT * 
FROM Customers 
WHERE LastName LIKE Replace('O''Brien,'''','%')

This should allow it to use an index as you are not modifying the original column.

Answer 2

Use parameters instead of building the queries in code.

If you are using ADO you can use a syntax like this:

Dim cmd, rs, connect, intNumber
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "your connectionstring"
cmd.CommandText = "SELECT * FROM Customers WHERE LastName LIKE @LastName"
cmd.Parameters.Append cmd.CreateParameter("@LastName",,,,"O'Brien")
Set rs = cmd.Execute

This should perform the query and insert the string O'Brien properly formatted for your database.

Using parameters ensures that all values are properly formatted and it also protects you against sql injection attacks.

Answer 3

For pure SQL, the escaping is entirely unnecessary.

SELECT * FROM Customers WHERE LastName = 'O''Brien'

Answer 4

If you want to stay correct and do this in SQL this is probably the best you can do

SELECT * FROM Customers WHERE 
LastName LIKE 'O%Brien' AND 
REPLACE(LastName,'''','') LIKE 'O''Brien'

You will still get table scans sometimes, due to poor selectivity.

The reason for the first where is to try to use an existing index. The reason for the second match is to ensure that last names like ObbBrien do not match.

Of course the best thing to do would be not to need the ugly replace. This could be achieved in the app by storing an additional clean lastname column. Or in a trigger. Or in an indexed view.