SQL calculates the largest incremental sub-sequence in a string.

SQL calculates the largest incremental sub-sequence in a string.

Returns the largest ascending subsequence of a string.

Database environment: SQL SERVER 2005

For example, find the largest incremental subsequence in the string "abcbklmnodfghijkmer. This string is a bit special,

It can contain only 26 lowercase letters a-z.

The general idea is as follows:

1. Convert the string to a column for storage and generate a row number.

2. Set an incremental counter column. The default value is 1. Compare the characters in the upper and lower rows. If the order in the dictionary is ascending,

The counter is incremented by 1. Otherwise, the counter is set to 1.

3. Find the maximum number of counters and the corresponding row number, and extract strings based on the two numbers.

The idea is ready, and the following code is directly posted:

DECLARE @ vtext VARCHAR (255) SET @ vtext = 'abcbklmnodfghijkmer '/* convert the string into a column for storage, and generate the row number */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)/* truncation string */select substring (@ vtext, start, sublen) AS largest 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

The largest subsequence is

Through the above ideas and code, we hope to enlighten and help you.