Dynamic Sql Search & Replace Function

@dmarkez asked a question yesterday, and just before I clicked the Post Your Answer button, he deleted the question. I think the answer is worth sharing... He didn't re-post the

Solution 1:

If you put all the search & replace values in one table, then you can write a few lines of SQL to cycle through the S&R values to fix the names. It's easy to add more S&R pairs to the prefix table, so your S&R routine is dynamic:

declare@prefixtable (srch varchar(255), rplc varchar(255))
declare@namestable (name varchar(255))

insertinto@prefixvalues ('te ', 'te'), ('de ', 'de'), ('van ', 'van'), ('dela ', 'dela'), ('san ', 'san'), ('o ', 'o'), ('mc ', 'mc'), ('los ', 'los')

insertinto@namesvalues ('van dam te mora te'), ('o mara dela cruz'), ('mc arthur white o san miguel'), ('moana te aro van dolf')

while (1=1)
beginupdate n
  set    n.name = replace(n.name, p.srch, p.rplc)
  from@names n,
         @prefix p
  where  (n.name like p.srch +'%') or (n.name like'% '+ p.srch +'%')

  if @@rowcount=0
    break
endselect*from@names

Notice the ('san ', 'san') comes before ('o ', 'o') in the prefix table. This is because san must be replaced before o, or osanmiguel will remain osan miguel. The order of the S&R is therefore important. You will need to add a clustered index to the prefix table that orders the S&R records correctly so that the S&R loop handles sub-prefixes first.