sql – 使用join替换varchar(max)字段中的值

我有一个包含带有占位符的文本字段的表.这样的事情

Row Notes  
1.  This is some notes ##placeholder130## this ##myPlaceholder##,#oneMore#. End.
2.  Second row...just a ##test#.

(此表平均包含大约1-5k行,一行的占位符平均数为5-15).

现在,我有一个查找表,如下所示：

Name             Value
placeholder130    Dog
myPlaceholder     Cat
oneMore           Cow
test              Horse

(查找表将包含从10k到100k记录的任何地方)

我需要找到将这些占位符从字符串加入查找表并用值替换的最快方法.所以,我的结果应该是这样(第1行)：

This is some notes Dog this Cat,Cow. End.

我想出的是为每个占位符将每行拆分为多个,然后将其加入查找表,然后将其连接到具有新值的原始行,但平均大约需要10-30秒.

解决方法

我第二个评论说,tsql不适合这个操作,但是如果你必须在db中这样做是一个使用函数来管理多个替换语句的例子.

由于您在每个音符(5-15)和非常大量的令牌(10k-100k)中具有相对较少的令牌,因此我的函数首先从输入中提取令牌作为潜在的令牌,并使用该集合来加入查找( dbo.Token下).在每个音符中查找您的任何令牌的发生情况太多了.

我做了一些perf测试,使用50k的令牌和5k的笔记,这个功能运行得很好,在< 2秒(在笔记本电脑上)完成.请报告此策略对您的影响. 注意：在您的示例数据中,令牌格式不一致(## _#,## _ ##,#_#),我猜这只是一个打字错误,并假定所有令牌采用## TokenName ##的形式.

--setup
    if object_id('dbo.[Lookup]') is not null
        drop table dbo.[Lookup];
    go
    if object_id('dbo.fn_ReplaceLookups') is not null
        drop function dbo.fn_ReplaceLookups;
    go
 
    create table dbo.[Lookup] (LookupName varchar(100) primary key,LookupValue varchar(100));
    insert into dbo.[Lookup]
        select '##placeholder130##','Dog' union all
        select '##myPlaceholder##','Cat' union all
        select '##oneMore##','Cow' union all
        select '##test##','Horse';
    go
 
    create function [dbo].[fn_ReplaceLookups](@input varchar(max))
    returns varchar(max)
    as
    begin
 
        declare @xml xml;
        select @xml = cast(('<r><i>'+replace(@input,'##','</i><i>')+'</i></r>') as xml);
 
        --extract the potential tokens
        declare @LookupsInString table (LookupName varchar(100) primary key);
        insert into @LookupsInString
            select  distinct '##'+v+'##'
            from    (   select  [v] = r.n.value('(./text())[1]','varchar(100)'),[r] = row_number() over (order by n)
                        from    @xml.nodes('r/i') r(n)
                    )d(v,r)
            where   r%2=0;
 
        --tokenize the input
        select  @input = replace(@input,l.LookupName,l.LookupValue)
        from    dbo.[Lookup] l
        join    @LookupsInString lis on 
                l.LookupName = lis.LookupName;
 
        return @input;
    end
    go          
    return            
 
--usage
    declare @Notes table ([Id] int primary key,notes varchar(100));
    insert into @Notes
        select 1,'This is some notes ##placeholder130## this ##myPlaceholder##,##oneMore##. End.' union all
        select 2,'Second row...just a ##test##.';
 
    select  *,dbo.fn_ReplaceLookups(notes)
    from    @Notes;

Tokenized
--------------------------------------------------------
This is some notes Dog this Cat,Cow. End.
Second row...just a Horse.

sql – 使用join替换varchar(max)字段中的值

解决方法

猜你在找的MsSQL相关文章