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 source:https://stackoverflow.com/questions/78151968/find-consecutive-alphabetical-characters-in-string
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.esproc.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/2bkGwqTj
Youtube 👉 https://www.youtube.com/@esProc_SPL