From SQL to SPL: Find consecutive alphabetical characters in string

consecutive alphabetical characters in string

The field of a certain database table is strings.

VALUE

Test

Test1

Tesssst

TTTTest

ABCDTest

Testuvwxyz

Now we need to find the strings that contain at least 5 consecutive letters sorted alphabetically, or in other words, the longest substring in a continuous ascending order with a length greater than or equal to 5. Note to exclude non-letter characters.

VALUE

ABCDTest

Testuvwxyz

SQL

SELECT value
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT MIN(lvl) AS start_pos
         FROM   (
           SELECT LEVEL AS lvl,
                  SUBSTR(t.value, LEVEL, 1) AS ch
           FROM   DUAL
           CONNECT BY LEVEL <= LENGTH(t.value)
         )
         MATCH_RECOGNIZE(
           ORDER BY lvl
           MEASURES
             FIRST(lvl) AS lvl
           PATTERN (first_row consecutive{3,})
           DEFINE first_row   AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z'),
                  consecutive AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z')
                                 AND ASCII(PREV(ch)) + 1 = ASCII(ch)
         )
         HAVING MIN(lvl) > 0
       )

SQL needs to use nested subqueries and CONNECT BY to simulate sequences, and then use MATCH_RECOGNIZE to process sequences. The code is cumbersome and difficult to understand. SPL directly provides sequence calculation functions:

https://try.esproc.com/splx?4yy


 A

1

$select * from table_name.txt

2

=A1. select(VALUE.split().group@i(~<=~[-1] || !isalpha(~[-1])).max(~.len())>=5)

A1 Load data.

A2 Split the string into a sequence by character, group the sequence, and start a new group when the current member is less than or equal to the previous member or the previous member is not a letter. Select the string with the longest group that has a length greater than or equal to 5. The function group can keep the grouped subsets for subsequent processing after grouping, rather than immediately aggregating them. @i represents grouping according to conditions.

Question sourcehttps://stackoverflow.com/questions/78151968/find-consecutive-alphabetical-characters-in-string