replace value in varchar(max) field with join

Question

I have a table that contains text field with placeholders. Something like this:

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

(This table contains about 1-5k rows on average. Average number of placeholders in one row is 5-15).

Now, I have a lookup table that looks like this:

Name             Value
placeholder130    Dog
myPlaceholder     Cat
oneMore           Cow
test              Horse   

(Lookup table will contain anywhere from 10k to 100k records)

I need to find the fastest way to join those placeholders from strings to a lookup table and replace with value. So, my result should look like this (1st row):

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

What I came up with was to split each row into multiple for each placeholder and then join it to lookup table and then concat records back to original row with new values, but it takes around 10-30 seconds on average.

Answer 1

You could try to split the string using a numbers table and rebuild it with for xml path.

select (
       select coalesce(L.Value, T.Value)
       from Numbers as N
         cross apply (select substring(Notes.notes, N.Number, charindex('##', Notes.notes + '##', N.Number) - N.Number)) as T(Value)
         left outer join Lookup as L
           on L.Name = T.Value
       where N.Number <= len(notes) and
             substring('##' + notes, Number, 2) = '##'
       order by N.Number
       for xml path(''), type
       ).value('text()[1]', 'varchar(max)')
from Notes

SQL Fiddle

I borrowed the string splitting from this blog post by Aaron Bertrand

Answer 2

SQL Server is not very fast with string manipulation, so this is probably best done client-side. Have the client load the entire lookup table, and replace the notes as they arrived.

Having said that, it can of course be done in SQL. Here's a solution with a recursive CTE. It performs one lookup per recursion step:

; with  Repl as
        (
        select  row_number() over (order by l.name) rn
        ,       Name
        ,       Value
        from    Lookup l
        )
,       Recurse as
        (
        select  Notes
        ,       0 as rn
        from    Notes
        union all
        select  replace(Notes, '##' + l.name + '##', l.value)
        ,       r.rn + 1
        from    Recurse r
        join    Repl l
        on      l.rn = r.rn + 1
        )
select  *
from    Recurse
where   rn = 
        (
        select  count(*)
        from    Lookup
        )
option  (maxrecursion 0)

Example at SQL Fiddle.

Another option is a while loop to keep replacing lookups until no more are found:

declare @notes table (notes varchar(max))

insert  @notes
select  Notes
from    Notes

while 1=1
    begin

    update  n
    set     Notes = replace(n.Notes, '##' + l.name + '##', l.value)
    from    @notes n
    outer apply
            (
            select  top 1 Name
            ,       Value
            from    Lookup l
            where   n.Notes like '%##' + l.name + '##%'
            ) l
    where   l.name is not null

    if @@rowcount = 0
        break
    end   

select  *
from    @notes

Example at SQL Fiddle.