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