Coding NATO International Radiotelephony Alphabet in SQL

Joe Celko and Ken Henderson are two of my favourite SQL Gurus and I very much enjoyed reading one of Joe’s articles this morning. It made me think and playing with the code was fun.

Try this on for size:

–series create
create table [dbo].[Series] (
[i] [int] identity (1, 1) not null,
constraint Series_i primary key clustered(i),
)
go
begin tran
declare @loop int;
set identity_insert series on
set @loop = 1;
while (@loop <= 1000) begin
insert into Series (i) values (@loop)
set @loop=@loop+1
end
set identity_insert series off
commit tran
select * from series

–create SpellingAlphabet view
CREATE VIEW SpellingAlphabet (Telephony, character)
AS
SELECT X.Telephony, X.character
FROM (VALUES (‘Alpha’,’a’), (‘Bravo’, ‘b’), (‘Charlie’, ‘c’),
(‘Delta’, ‘d’), (‘Echo’,’e’), (‘Foxtrot’, ‘f’), (‘Golf’, ‘g’),
(‘Hotel’, ‘h’), (‘India’, ‘i’), (‘Juliet’, ‘j’), (‘Kilo’, ‘k’),
(‘Lima’, ‘l’), (‘Mike’, ‘m’), (‘November’, ‘n’), (‘Oscar’, ‘o’),
(‘Papa’, ‘p’), (‘Quebec’, ‘q’), (‘Romeo’, ‘r’), (‘Sierra’, ‘s’),
(‘Tango’, ‘t’),(‘Uniform’, ‘u’), (‘Victor’, ‘v’), (‘Whiskey’, ‘w’),
(‘X-Ray’, ‘x’), (‘Yankee’, ‘y’), (‘Zulu’, ‘z’)
)
AS X (Telephony, character);
select * from SpellingAlphabet

–create the NATO (international radiotelephony) spelling alphabet code
declare @string nvarchar(300);
set @string = N’Joe Celko knows how to make SQL Server say uncle’;
SELECT telephony
FROM Series
INNER JOIN SpellingAlphabet
ON character = SUBSTRING(@string, i, 1)
WHERE i <= LEN(@string)
ORDER BY i

Here are the Results you’d get:

Juliet
Oscar
Echo
Charlie
Echo
Lima
Kilo
Oscar
Kilo
November
Oscar
Whiskey
Sierra
Hotel
Oscar
Whiskey
Tango
Oscar
Mike
Alpha
Kilo
Echo
Sierra
Quebec
Lima
Sierra
Echo
Romeo
Victor
Echo
Romeo
Sierra
Alpha
Yankee
Uniform
November
Charlie
Lima
Echo

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s