replace value in varchar(max) field with join

sql, sql-server, sql-server-2008-r2

Solution

I second the comment that tsql is just not suited for this operation, but if you must do it in the db here is an example using a function to manage the multiple replace statements.

Since you have a relatively small number of tokens in each note (5-15) and a very large number of tokens (10k-100k) my function first extracts tokens from the input as potential tokens and uses that set to join to your lookup (dbo.Token below). It was far too much work to look for an occurrence of any of your tokens in each note.

I did a bit of perf testing using 50k tokens and 5k notes and this function runs really well, completing in <2 seconds (on my laptop). Please report back how this strategy performs for you.

note: In your example data the token format was not consistent (`##_#, ##_##, #_#`), I am guessing this was simply a typo and assume all tokens take the form of ##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;

Returns:

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

Problem

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.

Original source