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
  • You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points. This is the LAST PUZZLE of this contest.
  • The scoring rules can be found here.
  • Solutions must be submitted as comments to this blog post.
  • Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result (for a phone number 1-800-123-4357):

PHONE
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

Apparently, the purpose of this exercise is to pick a string that sounds the best, so in this particular case we would pick: 1-800-123-HELP.

A correct answer (and workarounds!) will be published here in about a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

6 thoughts on “2018 Oracle SQL Puzzle of the Week #15

  1. KATAYAMA NAOTO April 30, 2018 / 5:48 pm

    WITH
    TEL_VIEW(TEL_NO)AS(SELECT ‘1-800-123-4357′ TEL_NO FROM DUAL)
    ,CNG_DIC(DIGIT,MAPSTO)AS(
    SELECT 1 DIGIT,’1′ MAPSTO 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)
    ,CNG_DICS(DIGIT,MAPSTOS) AS (
    SELECT DIGIT,TRIM(RTRIM(REGEXP_SUBSTR(CNG_DIC.MAPSTO,'[^,]*,|[^,]*$’,1,QTYCHK),’,’)) MAPSTOS
    FROM CNG_DIC
    CROSS JOIN XMLTABLE(‘for $i in 1 to $LENGTH_COL cast as xs:integer return $i’
    PASSING REGEXP_COUNT(CNG_DIC.MAPSTO,’,’)+1 AS LENGTH_COL COLUMNS QTYCHK NUMBER(32) PATH ‘.’))
    ,TEL_HEADER(TEN_NO)AS(SELECT REGEXP_REPLACE(TEL_NO,'([0-9]*-[0-9]*-[0-9]*-)([0-9]*)’,’\1′) TEN_NO FROM TEL_VIEW)
    ,TEL_FOOTER(TEN_NO)AS(SELECT REGEXP_REPLACE(TEL_NO,'([0-9]*-[0-9]*-[0-9]*-)([0-9]*)’,’\2′) TEN_NO FROM TEL_VIEW)
    ,TEL_FOOTERS(POS,TEN_NO)AS(
    SELECT QTYCHK POS,SUBSTR(TEN_NO,QTYCHK,1) TEN_NO
    FROM TEL_FOOTER
    CROSS JOIN XMLTABLE(‘for $i in 1 to $LENGTH_COL cast as xs:integer return $i’
    PASSING LENGTH(TEN_NO) AS LENGTH_COL COLUMNS QTYCHK NUMBER(32) PATH ‘.’))
    ,NO1(TEN_NO,MAPSTOS)AS(SELECT TEN_NO,MAPSTOS FROM TEL_FOOTERS A INNER JOIN CNG_DICS NO1 ON A.TEN_NO = NO1.DIGIT AND A.POS = 1)
    ,NO2(TEN_NO,MAPSTOS)AS(SELECT TEN_NO,MAPSTOS FROM TEL_FOOTERS A INNER JOIN CNG_DICS NO1 ON A.TEN_NO = NO1.DIGIT AND A.POS = 2)
    ,NO3(TEN_NO,MAPSTOS)AS(SELECT TEN_NO,MAPSTOS FROM TEL_FOOTERS A INNER JOIN CNG_DICS NO1 ON A.TEN_NO = NO1.DIGIT AND A.POS = 3)
    ,NO4(TEN_NO,MAPSTOS)AS(SELECT TEN_NO,MAPSTOS FROM TEL_FOOTERS A INNER JOIN CNG_DICS NO1 ON A.TEN_NO = NO1.DIGIT AND A.POS = 4)
    SELECT
    TEL_HEADER.TEN_NO||NO1.MAPSTOS||NO2.MAPSTOS||NO3.MAPSTOS||NO4.MAPSTOS PHONE
    FROM NO1 CROSS JOIN NO2 CROSS JOIN NO3 CROSS JOIN NO4 CROSS JOIN TEL_HEADER
    WHERE REGEXP_LIKE(NO1.MAPSTOS||NO2.MAPSTOS||NO3.MAPSTOS||NO4.MAPSTOS, ‘A|I|U|E|O’)
    ORDER BY NO1.MAPSTOS,NO2.MAPSTOS,NO3.MAPSTOS,NO4.MAPSTOS
    ;

    • KATAYAMA NAOTO April 30, 2018 / 6:42 pm

      If the vowel must be a single letter, correct the following part.

       WHERE REGEXP_LIKE(NO1.MAPSTOS||NO2.MAPSTOS||NO3.MAPSTOS||NO4.MAPSTOS, ‘A|I|U|E|O’)
        ↓
       WHERE REGEXP_COUNT(NO1.MAPSTOS||NO2.MAPSTOS||NO3.MAPSTOS||NO4.MAPSTOS, ‘A|I|U|E|O’) = 1

    • Zahar Hilkevich April 30, 2018 / 5:59 pm

      You are correct- I forgot to delete them- I will make a change

  2. pradeep May 8, 2018 / 11:30 pm

    unable to post queries

    • Zahar Hilkevich May 9, 2018 / 6:14 am

      do the queries in livesql.oracle.com and post a link here like Boobal Ganesan did

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