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

Viewed 529

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.

5 Answers
Related