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.