Method of the largest increment subsequence in a SQL computed string _mssql2005

To find the largest ascending subsequence in a string

Database environment: SQL SERVER 2005

As the title, the largest increment sequence in the string "Abcbklmnodfghijkmer" is obtained. This string is a bit special,

Consists of only 26 lowercase letters A-Z.

The general idea is as follows:

1. Move the string to a column store and generate the line number

2. Set an increment counter column, default to 1, compare the upper and lower rows of characters, if the order in the dictionary is incremented,

The counter adds 1, otherwise the counter is set to 1

3. Find the maximum number of counters and the corresponding line number, based on these 2 numbers to intercept the string

The idea has, the following directly paste code

DECLARE @vtext VARCHAR (255)
SET @vtext = ' Abcbklmnodfghijkmer '/
* Speak string into a column store, and generate line number * * with
x0 as
   ( SELECT number as ID,
      SUBSTRING (@vtext, number, 1) as Letter
    from  master.dbo.spt_values
    WHERE type = ' P '  C9/>and number <= LEN (@vtext)
      and number >= 1
    ),/* Implementation 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  ' as Clen from x0,
      x1
    whe RE x0.id = x1.id + 1
    )/
 * Intercept string
 /select SUBSTRING (@vtext, Start, Sublen) as maximum subsequence from
 (select ID, 
   clen,
      Max (Clen) over () as Maxclen,
      Id-max (Clen) over () + 1 as Start,
      Max (Clen) over () as Sub Len
    from  x1
   ) T
 WHERE Clen = Maxclen

The largest subsequence to be calculated is

Through the above ideas and code, I hope we can have some enlightenment and help.