Function to Turn Numbers into Words 1

[dpgirl]

I was wondering if anyone could be so kind to help me create a user-defined function that would do the following:

The function would take an input number from 1 to 9999999 and spit out every possible letter combination of these numbers based on the number-to-letter equivalents displayed on every phone. Here's the background on why I want to do this: My company has a whole bunch of 800 numbers in inventory that we use on our ads. We want to convert these numbers to vanity associations. That is, we want to make it easier for people to remember our phone numbers by providing word equivalents to the numbers. For example, 800-CALL-NOW rather than 800-225-669. I want to be able to enter the last 4-7 digits of every phone number into the function and have it return every possible letter combination. I can then review the output to see if any of the letter combinations actually spell out a valid word.

Here's the mapping in case anyone needs it:

1 = 1 [Doesn't have a letter association]
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 [Doesn't have a letter association]

Example:

exec mynewfunction(435) would produce:

GDJ
GDK
GDL
HDJ
HDK
HDL
IDJ
IDK
IDL
GEJ
etc.

 

[r937]

create table phonenumberletters
( num char(1) not null
, let char(1) not null
, primary key (num,let)
)
insert into phonenumberletters values ('1','1')
insert into phonenumberletters values ('2','A')
insert into phonenumberletters values ('2','B')
insert into phonenumberletters values ('2','C')
insert into phonenumberletters values ('3','D')
insert into phonenumberletters values ('3','E')
insert into phonenumberletters values ('3','F')
insert into phonenumberletters values ('4','G')
insert into phonenumberletters values ('4','H')
insert into phonenumberletters values ('4','I')
insert into phonenumberletters values ('5','J')
insert into phonenumberletters values ('5','K')
insert into phonenumberletters values ('5','L')
insert into phonenumberletters values ('6','M')
insert into phonenumberletters values ('6','N')
insert into phonenumberletters values ('6','O')
insert into phonenumberletters values ('7','P')
insert into phonenumberletters values ('7','R')
insert into phonenumberletters values ('7','S')
insert into phonenumberletters values ('8','T')
insert into phonenumberletters values ('8','U')
insert into phonenumberletters values ('8','V')
insert into phonenumberletters values ('9','W')
insert into phonenumberletters values ('9','X')
insert into phonenumberletters values ('9','Y')
insert into phonenumberletters values ('0','0')

select L1.let+L2.let+L3.let+L4.let as call
from phonenumberletters as L1
cross
join phonenumberletters as L2
cross
join phonenumberletters as L3
cross
join phonenumberletters as L4
where L1.num+L2.num+L3.num+L4.num = '7839'
ORDER BY 1

call
PTDW
PTDX
PTDY
PTEW
PTEX
PTEY
PTFW
PTFX
PTFY
PUDW
PUDX
PUDY
PUEW
PUEX
PUEY
PUFW
PUFX
PUFY
PVDW
PVDX
PVDY
PVEW
PVEX
PVEY
PVFW
PVFX
PVFY
RTDW
RTDX
RTDY
RTEW
RTEX
RTEY
RTFW
RTFX
RTFY
RUDW
RUDX
RUDY
RUEW
RUEX
RUEY
RUFW
RUFX
RUFY
RVDW
RVDX
RVDY
RVEW
RVEX
RVEY
RVFW
RVFX
RVFY
STDW
STDX
STDY
STEW
STEX
STEY
STFW
STFX
STFY
SUDW
SUDX
SUDY
SUEW
SUEX
SUEY
SUFW
SUFX
SUFY
SVDW
SVDX
SVDY
SVEW
SVEX
SVEY
SVFW
SVFX
SVFY


r937.com | rudy.ca

 

[ESquared]

If you have a dictionary database you can easily use some of the techniques from some anagram SQL teasers lying around on the internet to return only those that consist of one or more English words.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[MasterRacker]

I'm late to the party, but instead of 'working' you could also just have an intern type each of your numbers into this site:

http://www.phonespell.org/

;-)



_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]