WITH t1 AS
(SELECT 1 digit,
'1' mappings
FROM dual
UNION ALL SELECT 2,
'A,B,C'
FROM dual
UNION ALL SELECT 3,
'D,E,F'
FROM dual
UNION ALL SELECT 4,
'G,H,I'
FROM dual
UNION ALL SELECT 5,
'J,K,L'
FROM dual
UNION ALL SELECT 6,
'M,N,O'
FROM dual
UNION ALL SELECT 7,
'P,Q,R,S'
FROM dual
UNION ALL SELECT 8,
'T,U,V'
FROM dual
UNION ALL SELECT 9,
'W,X,Y,Z'
FROM dual
UNION ALL SELECT 0,
'0'
FROM dual),
t2 AS
(SELECT digit,
TRIM(REGEXP_SUBSTR(mappings, '[^,]+', 1, LEVEL)) mappings
FROM t1 CONNECT BY LEVEL <= REGEXP_COUNT(mappings, '[^,]+')
AND
PRIOR digit = digit
AND
PRIOR DBMS_RANDOM.VALUE IS NOT NULL),
t3 AS
(SELECT '1-800-123-4357' phno
FROM dual),
t4 AS
(SELECT phno,
substr(phno, -4, 1) col1,
substr(phno, -3, 1) col2,
substr(phno, -2, 1) col3,
substr(phno, -1, 1) col4
FROM t3),
t5 AS
(SELECT substr(t4.phno, 1, length(t4.phno)-4)||T5.MAPPINGS||t6.mappings||t7.mappings||t8.mappings phno
FROM t4,
t2 t5,
t2 t6,
t2 t7,
t2 t8
WHERE t4.col1 = t5.digit
AND t4.col2 = t6.digit
AND t4.col3 = t7.digit
AND t4.col4 = t8.digit)
SELECT phno
FROM t5
WHERE regexp_like(regexp_substr(phno, '[^-]+', 1, 4), '[aeiou]{1}', 'i')
AND NOT regexp_like(regexp_substr(phno, '[^-]+', 1, 4), '[aeiou]{2,}', 'i')| PHNO | 1-800-123-GEJP | 1-800-123-GEJQ | 1-800-123-GEJR | 1-800-123-GEJS | 1-800-123-GEKP | 1-800-123-GEKQ | 1-800-123-GEKR | 1-800-123-GEKS | 1-800-123-GELP | 1-800-123-GELQ | 1-800-123-GELR | 1-800-123-GELS | 1-800-123-HEJP | 1-800-123-HEJQ | 1-800-123-HEJR | 1-800-123-HEJS | 1-800-123-HEKP | 1-800-123-HEKQ | 1-800-123-HEKR | 1-800-123-HEKS | 1-800-123-HELP | 1-800-123-HELQ | 1-800-123-HELR | 1-800-123-HELS | 1-800-123-IDJP | 1-800-123-IDJQ | 1-800-123-IDJR | 1-800-123-IDJS | 1-800-123-IDKP | 1-800-123-IDKQ | 1-800-123-IDKR | 1-800-123-IDKS | 1-800-123-IDLP | 1-800-123-IDLQ | 1-800-123-IDLR | 1-800-123-IDLS | 1-800-123-IFJP | 1-800-123-IFJQ | 1-800-123-IFJR | 1-800-123-IFJS | 1-800-123-IFKP | 1-800-123-IFKQ | 1-800-123-IFKR | 1-800-123-IFKS | 1-800-123-IFLP | 1-800-123-IFLQ | 1-800-123-IFLR | 1-800-123-IFLS |
|---|