Блог

У меня есть этот код, который отлично работает на sql server 2019, но многие функции были введены, по-моему, только в версиях 2017 . Я использую sql server 2008r2 и хотел бы знать, может ли кто-нибудь помочь мне перекодировать его для запуска. Спасибо.

         declare 
            @s1 varchar(100)='The Elf on the Shelf: A Christmas Musical. (Touring)',
            @s2 varchar(100)='The Elf on the Shelf Musical, Baltimore';
        
        with words as (
          select 1 s, Replace(Translate(value,'!"*():;,.','|||||||||'),'|','') word
          from String_Split(@s1,' ')
          union all
          select 2, Replace(Translate(value,'!"*():;,.','|||||||||'),'|','')
        from String_Split(@s2,' ')
        ), matching as (
            select *, Row_Number() over(partition by word order by s) rn
            from words
        ), final as (
            select * , Count(*) over(partition by word, s) repeating, Count(*) over() * 1.0 totwords, sum(Iif(s=1,1,0)) over() s1words
            from matching
            outer apply(values(Iif(rn=2 and rn=s,1,0)))x(p)
        )
        select (Sum (p)   max(case when s=1 and repeating>1 then repeating end))
            / Max(Iif(totwords/s1words>0.5, totwords-s1words, s1words)) * 100 [Matching Words %]
        from final
 

Чтобы выполнить ту же работу в SQL Server 2008 R2, она будет намного менее эффективной, и вам понадобятся некоторые вспомогательные пользовательские функции, созданные.

Первый-универсальный для разделения строк, для замены STRING_SPLIT() . Существуют буквально сотни различных вариантов, но имейте в виду, что некоторые из них могут неправильно обрабатывать пространство в качестве разделителя. Это всего лишь первый, который я выхватил из своего запаса ресурсов по разделению строк:

 CREATE FUNCTION dbo.SplitStrings
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value(N'(./text())[1]', N'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, N'<i>' 
            REPLACE(@List, @Delimiter, N'</i><i>') 
            N'</i>').query(N'.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
 

Далее нам понадобится функция, которая выполняет итерацию по строке, по одному символу за раз, и удаляет плохие символы (это замена TRANSLATE() в вашем существующем коде). Некоторые решения будут использовать цикл while и patindex, и это нормально, но другие просто скажут вложить 17 REPLACE() вызовов, и это приведет к коду, который невозможно прочитать и поддерживать. Вот тот, который я придумал:

 CREATE FUNCTION dbo.CreateCleanString 
(
  @String        nvarchar(4000),
  @IgnorePattern nvarchar(255) 
)
RETURNS @Set TABLE
(
  listpos   int,
  character nchar(1)
)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @pos int, @ThisCharacter nchar(1);
  SET @pos = 1

  WHILE @pos <= LEN(@String)
  BEGIN
    SET @ThisCharacter = SUBSTRING(@String, @pos, 1);
    IF @IgnorePattern NOT LIKE N'%'   @ThisCharacter   N'%'
    BEGIN
      INSERT @Set(listpos, character)
        SELECT COALESCE(MAX(listpos)   1, 1), SUBSTRING(@String, @pos, 1)
        FROM @Set;
    END
    SET @pos = @pos   1;
  END
  RETURN;
END
GO
 

Имея эти два на месте, теперь вы можете написать этот гораздо более сложный запрос для достижения результатов. Я не собираюсь перечислять все различия, но они начинаются с самой первой строки (SQL Server 2008 не поддерживал встроенное объявление/набор) и продолжаются через все IIF() ссылки, переписанные в виде CASE выражений.

 DECLARE @pattern nvarchar(255), @s1 nvarchar(4000), @s2 nvarchar(4000);
          
SELECT @pattern = N'!"*():;,.',
       @s1 = N'The Elf on the Shelf: A Christmas Musical. (Touring)',
       @s2 = N'The Elf on the Shelf Musical, Baltimore';

;WITH clean_src AS -- break it apart by character and get rid of bad ones
(
  SELECT src.s, src.str, c.listpos, c.character 
    FROM (VALUES(1,@s1),(2,@s2)) AS src(s,str) 
    CROSS APPLY dbo.CreateCleanString(src.str, @pattern) AS c),
clean_output AS -- put it back together
(
  SELECT s, rn = ROW_NUMBER() OVER (PARTITION BY s ORDER BY c.listpos), 
    CleanString = (SELECT clean_src.character 
      FROM clean_src WHERE s = c.s ORDER BY c.listpos 
      FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(4000)') 
    FROM clean_src AS c),
words AS -- now break it apart again, this time into words
(
  SELECT s, word = Item, rn = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY s) 
    FROM clean_output CROSS APPLY dbo.SplitStrings(CleanString, ' ')
    WHERE rn = 1),
final AS -- do some matching and generate aggregates
(
  SELECT s, word, rn, x.p,
    repeating = COUNT(*) OVER (PARTITION BY word, s),
    totwords  = COUNT(*) OVER() * 1.0, 
    s1words   = SUM(CASE WHEN s = 1 THEN 1 ELSE 0 END) OVER()
  FROM words 
  OUTER APPLY (VALUES(CASE WHEN rn = 2 and rn = s THEN 1 ELSE 0 END)) AS x(p)
)
SELECT [Matching Words %] = (SUM(p) 
    MAX(CASE WHEN s = 1 AND repeating > 1  THEN repeating END))
  / MAX(CASE WHEN totwords / s1words > 0.5 THEN totwords - s1words 
  ELSE s1words END) * 100 
FROM final;
 

• Оригинальная версия на SQL Server 2019: db<>скрипка<>
• Этот метод в самой старой поддерживаемой версии (2014): db<>fiddle<>

Теперь у меня нет экземпляра SQL Server 2008 R2, на котором можно было бы это протестировать, и на самом деле я поспрашивал, и я не знаю никого, кто бы это делал. Действительно, пришло время подумать о том, чтобы наконец-то обновиться, ИМХО.