文字列中の最大の部分的インクリメンタルを計算する SQL メソッド

DECLARE @vtext VARCHAR(255)
SET @vtext = 'abcbklmnodfghijkmer'
/*Speak string into a column and generate row numbers*/
WITH x0
   AS ( SELECT number AS id ,
      SUBSTRING(@vtext, number, 1) AS letter
    FROM master.dbo.spt_values
    WHERE type = 'P'
      AND number <= LEN(@vtext)
      AND number >= 1
    ),/*implement counter*/
  x1 ( id, letter, clen )
   AS ( SELECT id ,
      letter ,
      1 AS clen
    FROM x0
    WHERE id = 1
    UNION ALL
    SELECT x0.id ,
      x0.letter ,
      CASE WHEN x1.letter <= x0.letter THEN x1.clen + 1
        ELSE 1
      END AS clen
    FROM x0 ,
      x1
    WHERE x0.id = x1.id + 1
    )
 /*intercept string*/
 SELECT SUBSTRING(@vtext, start, sublen) AS max subsequence
 FROM ( SELECT id ,
      clen ,
      MAX(clen) OVER ( ) AS maxclen ,
      id - MAX(clen) OVER ( ) + 1 AS start ,
      MAX(clen) OVER ( ) AS sublen
    FROM x1
   ) t
 WHERE clen = maxclen