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.
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Further Reading:
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.