SQL replace statement too slow

Question

I have a replace statement that does something like this:

SELECT Distinct Forenames, Surname, dbUSNs.DateOfBirth, Datasetname, 
       dbUSNs.MoPIGrade, SourceAddress, VRM, URNs 
FROM Person 
WHERE ( Replace(Replace(Replace(Replace(Replace(Replace(Replace
        (Replace(Replace(Replace(Replace(Replace(Replace(Replace
        (Replace(Replace(Replace(Replace(Replace(Replace(Replace
        (Replace(Replace(Replace(Replace
        (Surname,'/',''''),'?',''''),'',''''),'^',''''),'{',''''),'}',''''),
                '[',''''),']',''''),';',''''),'$',''''),'=',''''),'*',''''),
                '#',''''),'|',''''),'&',''''),'@',''''),'\',''''),'<',''''),
                '>',''''),'(',''''),')',''''),'+',''''),',',''''),'.',''''),
                ' ','''') LIKE 'OREILLY%')

Therefore even though OReilly is passed, O'Reilly will be found. However, this is too slow. Is there a better way of approaching it?

Answer 1

The problem isn't that REPLACE is "too slow", but that using it at all makes that part of the query unsargable, meaning that it can't use an index.

Wikipedia: Sargable

Basically you've forced a tablescan / indexscan, from top to bottom. On top of that you have the overhead of REPLACE.

If you want this query to run fast, I would instead do one of the following:

• Create an additional column containing a searchable text version of the Surname
• Create an indexed, materialized view with those REPLACE functions