3 Solutions to 2018 Oracle SQL Puzzle of the Week #15

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s