SQL SELECT ... WHERE with REPLACE - worries that it is inefficient

Question

In SQL Server 2005, I have a product search that looks like this:

select ProductID, Name, Email from Product where Name = @Name 

I was asked to ignore a couple of "special" characters in Product.Name, so the search for "Potato" returns "Pot-fingers" as well as "Potato". My first thought is to just do this:

 select ProductID, Name, Email from Product where REPLACE(Name, '-', '') = @Name 

... but, on the other hand, I wonder if I can kill performance by running a function on EVERY candidate. Does SQL have some kind of optimization magic that can help me deal with this quickly? Can you come up with something simpler, can I try with the requirements that I have?

Answer 1

More standards-based: you can add a new column, such as searchable_name , pre-calculate the results of REPLACE (and any other settings, such as SOUNDEX), on INSERT/UPDATE and save them in a new column, then search on that column.

Less standards-based: many DBMSs provide a function in which you can create INDEX using a function; this is often called a functional index. Your situation is well suited for such a function.

Most powerful / flexible: use a special search tool such as Lucene. This may seem redundant for this situation, but they were designed for search, and most of them offer sophisticated generation algorithms that almost certainly solve this problem.

Answer 2

Most likely, you will get better performance if you want the first character to be alphabetic, for example ...

 select ProductID, Name, Email from Product where REPLACE(Name, '-', '') = @Name And Name Like Left(@Name, 1) + '%' 

If the name column is indexed, you will most likely receive an index query instead of a scan. The disadvantage is that you will not return strings where the value is "-po-ta-to-es", because the first character does not match.

Answer 3

Can you add a field to the product table with a search version of the product name with special characters that are already deleted? Then you can perform a “replacement” only once for each record and perform effective searches against the new field.