sqloracle-database

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