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
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 |