SQL Server - Is there an easy way to ignore quotes when searching?

Question

I have a site where I need to be able to search for data and have the query ignore all quotes.

1. Search for don't, don’t or dont and retrieve results for rows that have words that start with: don't, don’t or dont
2. Search for "hello" or “hello” or hello and retrieve results for rows that have words that start with: "hello", “hello” or hello

Note: I already am stripping out the quotes for the passed in search term

I want to know if there is an easier (or less verbose) method than:

select Name 
  from tbl_MyTable
 where (Replace(Replace(Replace(Replace(Replace(Replace(Name,'“',''),'‘',''),'''',''),'"',''),'’',''),'”','') like 'dont%' 
    or Replace(Replace(Replace(Replace(Replace(Replace(Name,'“',''),'‘',''),'''',''),'"',''),'’',''),'”','') like '% dont%' );

Right now, my best idea is to create a new column that contains the quote-stripped version (prepended with a space) so that I can just do:

select Name 
  from tbl_MyTable
 where FixedName like '% dont%';

But I would really like to know if this can be accomplished without creating a new column and have it be efficient.

Answer 1

Use a fulltext index instead of LIKE.

Create your fulltext index:

http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX ix1 ON tbl_MyTable(YourKey); //unique index required
CREATE FULLTEXT CATALOG ft AS DEFAULT; // ft is your freetext catalog name
CREATE FULLTEXT INDEX ON tbl_MyTable(Name) 
   KEY INDEX ix1
   WITH STOPLIST = SYSTEM; // this is your index and allows you to run the command below

Then use this to run your query:

SELECT Name 
FROM tbl_MyTable 
WHERE FREETEXT(Name, 'dont');

That's the fastest technique for this kind of thing. You can get even faster if you use third party free-text engines but there's probably no need for that.