动态SQL搜索&替换函数 [英] Dynamic SQL Search & Replace Function
问题描述
@dmarkez昨天问了一个问题,就在我点击发布您的答案
按钮之前,他删除了这个问题。我认为答案值得分享......他没有重新提出这个问题,所以我希望他不介意我是否重新发布这个问题,以便我可以与其他尝试类似的人分享答案:
原始标题
MS SQL函数操作字符串
原始问题
我需要一个MS SQL函数,它需要确定具有前缀的名称,然后将其与实际名称结合。
什么是最好的方法或函数来实现它?
前缀名称:Te,De,Van,Dela,O,Mc,San,Los,等等...
示例输入/输出名称:
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
如果您将所有搜索&替换一个表中的值,然后您可以编写几行SQL来循环显示S& R值以修复名称。向前缀
表添加更多S& R对是很容易的,因此您的S& R例程是动态的:
<$ ($ var $($)$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'插入@prefix
值('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.
这篇关于动态SQL搜索&替换函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!