# 800 Phone Puzzle

For a given 800 phone number (like 1-800-123-4567) find all number-letter representations.

• Use a single SELECT statement only.
• Only last 4 digits of the phone number have to be replaced with letters.
• Exactly 1 letter (out of 4) must be vowel,  the rest – consonant
• The following table shows all possible mappings:
Digit Maps to
1 1
2 A, B, C
3 D, E, F
4 G, H, I
5 J, K, L
6 M, N, O
7 P, Q, R, S
8 T, U, V
9 W, X, Y, Z
0 0

# Solutions:

Essentially, all solutions below share the same idea of generating the letter based phone numbers. The differences are in a way the mapping CTE is created and a way to limit the number of vowels to 1.

### Solution #1. Compact form of creating the map CTE with recursive check for the vowels:

``````WITH map AS (
SELECT digit, letter, '4357' phone
FROM TABLE(sys.odcivarchar2list('00','11','2ABC','3DEF','4GHI',
'5JKL','6MNO','7PQRS','8TUV','9WXYZ')) t,
LATERAL(SELECT SUBSTR(t.column_value,1,1) digit,
SUBSTR(t.column_value,1+LEVEL,1) letter
FROM dual
CONNECT BY SUBSTR(t.column_value,1+LEVEL,1) IS NOT NULL) x
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1``````

### Solution #2. Using more efficient way of creating the map CTE :

```WITH x AS (
SELECT ROWNUM-1 digit,COLUMN_VALUE letters
FROM TABLE(sys.odcivarchar2list('0','1','ABC','DEF','GHI','JKL',
'MNO','PQRS','TUV','WXYZ'))
), map AS (
SELECT digit, SUBSTR(letters, level, 1) letter, '4357' phone
FROM x
CONNECT BY SUBSTR(letters, level, 1) IS NOT NULL
AND PRIOR digit = digit
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1```

### Solution #3. Much more efficient way of creating the map CTE and using Regular Expression to limit the vowels :

```WITH d AS (
SELECT LEVEL+1 n, CASE WHEN LEVEL+1 IN (7,9) THEN 4 ELSE 3 END cnt,
'4357' phone
FROM dual
CONNECT BY LEVEL<=8
), a AS (
SELECT CHR(ASCII('A')+LEVEL-1) letter, ROWNUM rn
FROM dual
CONNECT BY CHR(ASCII('A')+LEVEL-1)<='Z'
), x AS (
SELECT n,
1+NVL(SUM(cnt) OVER(ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING),0) c1,
SUM(cnt) OVER(ORDER BY n) c2,
phone
FROM d
), map AS (
SELECT n digit, letter, x.phone
FROM x JOIN a ON a.rn BETWEEN x.c1 AND x.c2
UNION
SELECT ROWNUM-1, TO_CHAR(ROWNUM-1), x.phone
FROM x
WHERE ROWNUM<=2
), res(str, lvl) AS (
SELECT letter, 1
FROM map
WHERE SUBSTR(map.phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1
FROM res JOIN map ON SUBSTR(map.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(map.phone)
AND REGEXP_COUNT(res.str || letter,'[AEIOU]')<=1
)
SELECT str phone
FROM res
WHERE lvl=4
AND REGEXP_COUNT(str,'[AEIOU]')=1```

You can execute the above SQL statements in Oracle Live SQL environment.