SQL SELECT... WHERE with REPLACE - worried that it's inefficient

Question

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

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

I've been asked to ignore a couple "special" characters in Product.Name, so that a search for "Potatoes" returns "Po-ta-toes" as well as "Potatoes". My first thought is to just do this:

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

...but on second thought, I wonder if I'm killing performance by running a function on EVERY candidate result. Does SQL have some optimization magic that help it do this kind of thing quickly? Can you think of anything easier I might be able to try with the requirements I have?

Answer 1

You will likely get better performance if you are willing to force the first character to be alphabetic, like this...

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

If the name column is indexed, you will likely get an index seek instead of a scan. The downside is, you will not return rows where the value is "-po-ta-to-es" because the first character does not match.

Answer 2

More standards-based: You could add a new column, e.g., searchable_name, precalculate the results of the REPLACE (and any other tweaks, e.g., SOUNDEX) on INSERT/UPDATE and store them in the new column, then search against that column.

Less standards-based: Lots of RDBMS provide a feature where you can create an INDEX using a function; this is often called a functional index. Your situation seems fairly well suited to such a feature.

Most powerful/flexible: Use a dedicated search tool such as Lucene. It might seem overkill for this situation, but they were designed for searching, and most offer sophisticated stemming algorithms that would almost certainly solve this problem.

Answer 3

Can you add a field to your product table with a search-able version of the product name with special characters already removed? Then you can do the 'replace' only once for each record, and do efficient searches against the new field.