Problem Description

Table table_name has a single string field VALUE (max length 50). Find strings that contain at least 5 consecutive letters in ascending alphabetical order. That is: for each string, extract all letters, check them in original order, find the longest consecutive ascending subsequence (each letter >= previous one), and keep rows where the length >= 5. Non-letter characters must be excluded.

Source Data

Source Data
Expected Result

Expected Result

Two qualifying strings as examples:

ABCDTest: the letters are A, B, C, D, T, e, s, t. The first four A-B-C-D are consecutively ascending. Then T (ASCII 84) is still greater than D, so the ascending segment extends to A-B-C-D-T (5 letters). But the next letter e (ASCII 101) is greater than T (84), so the ascent continues. The entire letter sequence A-B-C-D-T-e-s-t is ascending, with a max run length of 8. In fact >= 5 is sufficient to qualify.

Testuvwxyz: the letters are T, e, s, t, u, v, w, x, y, z. Among them, u-v-w-x-y-z has 6 consecutive ascending letters, length >= 5, qualifying.

SQLazy Step-by-step Implementation

Core idea: first expand by string length into one row per character, filter out non-letter characters, then use segment's not_up condition to detect where the ascending order breaks (start a new group when the current character is less than the previous one), then aggregate by group size, and finally filter strings whose max run >= 5. This pattern of character expansion, segmentation, and aggregation filtering is a typical SQLazy paradigm for string sequence problems.

SQLazy Step-by-step Implementation
【Online operation case】

The steps are explained below.

Step 1: Expand rows by max string length

expand 50 as Position

Expand each string into 50 rows, with Position ranging 1~50. This is a preprocessing step for strings, enabling subsequent per-character computation. Since the maximum string length is 50, expand 50 covers all character positions.

expand 50 as Position

Step 2: Extract character at each position

compute mid(VALUE, Position, 1) as Ch

Use the mid function to extract 1 character at Position from VALUE, named Ch. For positions beyond the actual string length, mid returns empty.

compute mid

Step 3: Filter out non-letter characters

filter (Chisalpha)

Use the isalpha function to keep only letters (A-Z, a-z), removing empty characters, digits, and symbols.

filter

Step 4: Detect ascending breaks (core)

segment Ch; not_up; partition VALUE; as GrpNo

This is the most critical step. The segment statement partitions by VALUE and checks the Ch sequence within each partition: not_up specifies the non-ascending condition - when the current character (ASCII value) is less than or equal to the previous one, a segment break is triggered. Each time the ascending order breaks, a new group starts, generating group number GrpNo. Consecutively ascending letters are assigned to the same group.

Step 5: Count characters per group

summarize Ch count as GrpSize; group VALUE, GrpNo

Group by VALUE and GrpNo, count the number of letters in each group as GrpSize.

Count characters

summarize

Step 6: Get max group length per string

summarize GrpSize max as MaxRun; group VALUE

Group by VALUE, take the maximum GrpSize to get the longest consecutive ascending segment length MaxRun for each string.

summarize GrpSize

Step 7: Filter strings with length >= 5

filter (MaxRun >= 5)

Keep rows where MaxRun >= 5.

filter

Step 8: Keep final result columns

Generated SQL
After confirming the above steps, the SQLazy compiler automatically generates native SQL (Oracle syntax):

WITH TEMP_TABLE__2 AS ( SELECT 1 AS col_1 FROM DUAL UNION ALL SELECT col_1 + 1 FROM TEMP_TABLE__2 WHERE col_1 < 50 ), t1 AS ( SELECT table_name.VALUE, col_1 FROM table_name CROSS JOIN TEMP_TABLE__2 ), t2 AS ( SELECT VALUE, Position, SUBSTR(VALUE, Position, 1) AS Ch FROM t1 ), t3 AS ( SELECT VALUE, Position, Ch FROM t2 WHERE REGEXP_LIKE(Ch, '^[A-Za-z]+$') ), t5 AS ( SELECT VALUE, GrpNo, COUNT(Ch) AS GrpSize FROM ( SELECT VALUE, Position, Ch, SUM(CASE WHEN Ch <= col__5 THEN 1 ELSE 0 END) OVER (PARTITION BY VALUE ORDER BY VALUE ROWS UNBOUNDED PRECEDING) + 1 AS GrpNo FROM (SELECT t3.*, LAG(Ch,1) OVER (PARTITION BY VALUE ORDER BY VALUE) AS col__5 FROM t3) sub ) t4 GROUP BY VALUE, GrpNo ), t6 AS ( SELECT VALUE, MAX(GrpSize) AS MaxRun FROM t5 GROUP BY VALUE ) SELECT VALUE FROM t6 WHERE MaxRun >= 5

SQLazy lets you describe logic in business language instead of writing nested queries in SQL syntax. In this example of finding consecutive alphabetical letters, SQLazy uses 8 steps to clearly describe the entire flow from character expansion to segment aggregation. The segment not_up condition directly expresses the semantics of starting a new group when alphabetical order breaks, simplifying what would otherwise require recursive CTE + LAG + SUM window functions into a single readable declaration. The expand row expansion combined with per-character computation makes string processing as intuitive as table row operations; the step-by-step computation model ensures every step from character extraction to grouping and filtering is traceable and debuggable.

Official Links
SQLazy Online Experience: sqlazy.com (free, no registration required)
SQLazy Repository: github.com/SPLWare/SQLazy