EN VI

Sql - Find consecutive alphabetical characters in string?

How to Sql - Find consecutive alphabetical characters in string

I have a requirement where I need to find the consecutive alphabetical characters in the string in Oracle SQL. I have tried regular expressions but it is not working.

Table Name - Test_Dummy(var_col)

Values in Var_Col 
Test
Test1
Tesssst
TTTTest
ABCDTest

Select * from test_dummy where regexp_like(var_col, '[A-Za-z]{5,}');

The output I am getting is all the strings with more than 5 characters.

I used below regexp to get the repeated characters -

Select * from test_dummy where regexp_like(var_col,'(.+1)\1{3,}?');

Result :

TTTTest
Tesssst

I need to find consecutive data i.e. ABCDTest

Solution:

You can split the string into individual characters and then use MATCH_RECOGNIZE (from Oracle 12 onwards) to perform row-by-row pattern matching to look for consecutive ASCII values:

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
       )

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'Test'     FROM DUAL UNION ALL
SELECT 'Test1'    FROM DUAL UNION ALL
SELECT 'Tesssst'  FROM DUAL UNION ALL
SELECT 'TTTTest'  FROM DUAL UNION ALL
SELECT 'ABCDTest' FROM DUAL UNION ALL
SELECT 'Testuvwxyz' FROM DUAL;

Outputs:

VALUE
ABCDTest
Testuvwxyz

fiddle