如果您将所有搜索&替换一个表中的值，然后您可以编写几行SQL来循环显示S& R值以修复名称。向前缀表添加更多S& R对是很容易的，因此您的S& R例程是动态的：

值（'te'，'te'），（'de'，'de'），（'van'，'van'），（'dela'，'dela'），（ ''，'san'），（'o'，'o'），（'mc'，'mc'），（'los'，'los'）

插入@names
值（'van dam te mora te'），（'o mara dela cruz'），（'mc arthur white o san san miguel'），（'moana te aro van dolf'）

while（1 = 1）
begin
update 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
end

select @ from @names

注意（'o'，'o'）之前的（'san'，'san'） >在前缀表中。这是因为 san 必须在 o 或 osanmiguel 之前被替换>将保持 osan miguel 。因此，S& R的顺序非常重要。您需要将聚集索引添加到前缀表中，以正确地订购S& R记录，以便S& R循环首先处理子前缀。

@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 question, so I hope he doesn't mind if I re-post it so that I can share the answer with anyone else who is trying something similar:

Original Title

MS SQL function to manipulate string

Original Question

I need a MS SQL function that needs to determine names with prefix then combine this with actual names.

What is the best method or function to achieve this?

Prefix names: Te, De, Van, Dela, O, Mc, San, Los, etc…

Sample Input/Output Names:

van dam te mora te-> vandam temora te

o mara dela cruz -> omara delacruz

mc arthur white o san miguel -> mcarthur white osanmiguel

moana te aro van dolf-> moana tearo vandolf

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 @prefix table (srch varchar(255), rplc varchar(255))
declare @names table (name varchar(255))

insert into @prefix
values ('te ', 'te'), ('de ', 'de'), ('van ', 'van'), ('dela ', 'dela'), ('san ', 'san'), ('o ', 'o'), ('mc ', 'mc'), ('los ', 'los')

insert into @names
values ('van dam te mora te'), ('o mara dela cruz'), ('mc arthur white o san miguel'), ('moana te aro van dolf')

while (1=1)
begin
  update 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
end

select * 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.