To find the largest increment subsequence in a string

Database environment: SQL SERVER 2005

Title, the largest increment subsequence in the string "Abcbklmnodfghijkmer" is obtained. This string is a bit special,

Consists of only 26 lowercase letters A-Z.

The approximate 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, to compare the characters of the upper and lower lines, if the order in the dictionary is incremented,

The counter adds 1, otherwise the counter is 1

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

The idea has, the following directly paste code

DECLARE @vtext VARCHAR(255)SET @vtext = 'Abcbklmnodfghijkmer'/*tells the string to be stored in a column and generates a line number*/ withx0 as(SELECT    Number  asID,SUBSTRING(@vtext, Number,1) as Letter frommaster.dbo.spt_valuesWHEREType= 'P'                         and  Number <= LEN(@vtext)                         and  Number >= 1             ),/*Implementing Counters*/X1 (ID, letter, Clen) as(SELECTID, letter,1  asClen fromx0WHEREId= 1               UNION  All               SELECTx0.id, X0.letter, Case  whenX1.letter<=X0.letter ThenX1.clen+ 1                             ELSE 1                        END  asClen fromx0, X1WHEREX0.id=X1.id+ 1             )    /*Intercept String*/    SELECT  SUBSTRING(@vtext, Start, Sublen) asMaximum sub-sequence from(SELECTID, Clen,MAX(Clen) Over( ) asMaxclen, id- MAX(Clen) Over( )+ 1  asStart,MAX(Clen) Over( ) asSublen fromx1) TWHEREClen=Maxclen

The maximum number of sub-sequences to be calculated is

(End of this article)

To find the largest increment subsequence in a string