Замените значение в поле varchar (max) с соединением

Ответ 1

Во-вторых, комментарий, что tsql просто не подходит для этой операции, но если вы должны сделать это в db, вот пример использования функции управления несколькими операциями замены.

Поскольку у вас относительно небольшое количество токенов в каждой ноте (5-15) и очень большое количество токенов (10k-100k), моя функция сначала извлекает маркеры из ввода в качестве потенциальных токенов и использует их, чтобы присоединиться к ваш поиск (dbo.Token ниже). Было слишком много работы, чтобы искать появление любого из ваших жетонов в каждой ноте.

Я немного поработал с использованием токенов 50 тыс. и 5 тыс. нот, и эта функция работает очень хорошо, завершаясь за 2 секунды (на моем ноутбуке). Пожалуйста, сообщите, как эта стратегия работает для вас.

note: В ваших данных примера формат токена не был согласован (##_#, ##_##, #_#), я предполагаю, что это была просто опечатка и предположил, что все токены имеют форму ## 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.

Ответ 2

Вы можете попытаться разбить строку с помощью таблицы чисел и перестроить ее с помощью 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

Я заимствовал строку, разделяющую этот пост в блоге Аарона Бертран

Ответ 3

SQL Server не очень быстр с манипуляциями с строками, поэтому это, вероятно, лучше всего сделать на стороне клиента. Попросите клиента загрузить всю таблицу поиска и замените заметки по мере их поступления.

Сказав это, это, конечно, можно сделать в SQL. Здесь решение с рекурсивным CTE. Он выполняет один поиск на каждом этапе рекурсии:

; 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)

Пример в SQL Fiddle.

Другим вариантом является цикл while, чтобы продолжать замену поисковых запросов, пока не будет найдено больше:

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

Пример в SQL Fiddle.

Ответ 4

Попробуйте это

;WITH CTE (org, calc, [Notes], [level]) AS
(
    SELECT [Notes], [Notes], CONVERT(varchar(MAX),[Notes]), 0 FROM PlaceholderTable

    UNION ALL

    SELECT  CTE.org, CTE.[Notes],
        CONVERT(varchar(MAX), REPLACE(CTE.[Notes],'##' + T.[Name] + '##', T.[Value])), CTE.[level] + 1
    FROM    CTE
    INNER JOIN LookupTable T ON CTE.[Notes] LIKE '%##' + T.[Name] + '##%'

)

SELECT DISTINCT org, [Notes], level FROM CTE
WHERE [level] = (SELECT MAX(level) FROM CTE c WHERE CTE.org = c.org)

SQL FIDDLE DEMO

Проверьте приведенную ниже ссылку devioblog

сообщение devioblog

Ответ 5

Чтобы получить скорость, вы можете предварительно обработать шаблоны заметок в более эффективную форму. Это будет последовательность фрагментов, каждая из которых заканчивается заменой. Замена может быть NULL для последнего фрагмента.

Notes
Id     FragSeq    Text                    SubsId
1      1          'This is some notes '   1
1      2          ' this '                2
1      3          ', '                    3
1      4          '. End.'                null
2      1          'Second row...just a '  4
2      2          '.'                     null

Subs
Id  Name               Value
1   'placeholder130'   'Dog'
2   'myPlaceholder'    'Cat'
3   'oneMore'          'Cow'
4   'test'             'Horse'  

Теперь мы можем делать замены с простым соединением.

SELECT Notes.Text + COALESCE(Subs.Value, '') 
FROM Notes LEFT JOIN Subs 
ON SubsId = Subs.Id WHERE Notes.Id = ?
ORDER BY FragSeq

Создает список фрагментов с завершенными подстановками. Я не являюсь пользователем MSQL, но в большинстве диалектов SQL вы можете легко конкатенировать эти фрагменты в переменной:

DECLARE @Note VARCHAR(8000)
SELECT @Note = COALESCE(@Note, '') + Notes.Text + COALSCE(Subs.Value, '') 
FROM Notes LEFT JOIN Subs 
ON SubsId = Subs.Id WHERE Notes.Id = ?
ORDER BY FragSeq

Предварительная обработка шаблона заметок на фрагменты будет понятна с использованием методов разделения строк других сообщений.

К сожалению, я не в том месте, где я могу проверить это, но он должен работать нормально.

Ответ 6

Я действительно не знаю, как он будет работать с 10k + поисков. как работает старый динамический SQL?

DECLARE @sqlCommand  NVARCHAR(MAX)
SELECT @sqlCommand  = N'PlaceholderTable.[Notes]'

SELECT @sqlCommand  = 'REPLACE( ' + @sqlCommand  + 
                      ', ''##' + LookupTable.[Name] + '##'', ''' + 
                      LookupTable.[Value] + ''')'  
FROM LookupTable

SELECT @sqlCommand  = 'SELECT *, ' + @sqlCommand  + ' FROM PlaceholderTable'

EXECUTE sp_executesql @sqlCommand

Скриншот демо

Ответ 7

И теперь для некоторого рекурсивного CTE.

Если ваши индексы настроены правильно, это должно быть очень быстро или очень медленно. SQL Server всегда удивляет меня экстремальными по производительности, когда дело доходит до r-CTE...

;WITH T AS (
  SELECT
    Row,
    StartIdx = 1,                                  -- 1 as first starting index
    EndIdx = CAST(patindex('%##%', Notes) as int), -- first ending index
    Result = substring(Notes, 1, patindex('%##%', Notes) - 1)
                                                   -- (first) temp result bounded by indexes
  FROM PlaceholderTable -- **this is your source table**
  UNION ALL
  SELECT
    pt.Row,
    StartIdx = newstartidx,                        -- starting index (calculated in calc1)
    EndIdx = EndIdx + CAST(newendidx as int) + 1,  -- ending index (calculated in calc4 + total offset)
    Result = Result + CAST(ISNULL(newtokensub, newtoken) as nvarchar(max))
                                                   -- temp result taken from subquery or original
  FROM 
    T
    JOIN PlaceholderTable pt -- **this is your source table**
      ON pt.Row = T.Row
    CROSS APPLY(
      SELECT newstartidx = EndIdx + 2              -- new starting index moved by 2 from last end ('##')
    ) calc1
    CROSS APPLY(
      SELECT newtxt = substring(pt.Notes, newstartidx, len(pt.Notes))
                                                   -- current piece of txt we work on
    ) calc2
    CROSS APPLY(
      SELECT patidx = patindex('%##%', newtxt)     -- current index of '##'
    ) calc3
    CROSS APPLY(
      SELECT newendidx = CASE 
        WHEN patidx = 0 THEN len(newtxt) + 1
        ELSE patidx END                            -- if last piece of txt, end with its length
    ) calc4
    CROSS APPLY(
      SELECT newtoken = substring(pt.Notes, newstartidx, newendidx - 1)
                                                   -- get the new token
    ) calc5
    OUTER APPLY(
      SELECT newtokensub = Value
      FROM LookupTable
      WHERE Name = newtoken                        -- substitute the token if you can find it in **your lookup table**
    ) calc6
  WHERE newstartidx + len(newtxt) - 1  <= len(pt.Notes)  
                                                   -- do this while {new starting index} + {length of txt we work on} exceeds total length
) 
,lastProcessed AS (
  SELECT 
    Row, 
    Result,
    rn = row_number() over(partition by Row order by StartIdx desc)
  FROM T 
)                                                  -- enumerate all (including intermediate) results
SELECT *
FROM lastProcessed
WHERE rn = 1                                       -- filter out intermediate results (display only last ones)