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

Interview with Myt-Sentril

Extracted from the Introduction
to
Prophecies
by
Myt-Sentril of Vos.
23rd Printing, 3496

Little is known about the life of the famed Myt-Sentril of Vos as his popularity did not come until several decades after his death. Aside from the official citizen demographic information the only reputed source material about the man is the famous but still only alleged interview at Thon Publishing, the publishing house where the first edition of Prophecies was printed. If this is, indeed, authentic we have an editor named O-Tourn to thank for this, although she obviously didn’t publish this herself. The document was found within papers sold to the Museum of Tuik at auction in 3406.
The interview was dated 2243, matching the year Prophecies was first printed and authenticated with O-Tourn’s signature. There is no reason to presume that it is a fake although some scholars have argued this. The paper is certainly old and the ink is beginning to fade; although no one has been able to date it precisely. It certainly seems to match the age of the manuscript which most scholars agree is the genuine final draft by Myt-Sentril.
Handwriting analysts believe the body of the interview to have been hand-written by someone other than the editor. This is entirely plausible for the time period. It was common practise for a staff recorder to be brought in to preserve the interview. Unfortunately there are no outside records of the interview or the editor being assigned to Myt-Sentril although historical documents from Thon Publishing do corroborate Cilla O-Tourn as an employee in 2248. Her note at the end is most compelling, there is an odd juxtaposition that the reader may take from it. As if O-Tourn had begun to second guess her conviction that Myt-Sentril’s book was a work of fiction.
Please take the opportunity to judge for yourself.

S. Given, Master of Lirdan Literature
Tuik University
October 32, 3495

A conversation with Myt-Sentril of Vos

Ed. – Myt-Sentril. It is a pleasure to finally meet you. Part of your fee for publishing here includes my input as editor during this interview, even for the self-published works, we would usually make suggestions for improvement. This is our policy and helps to further our reputation as a publisher of quality work. I, however, must admit that I do not have any suggestions for your text. In fact, we have already begun to print it and it should be ready in two weeks. I invited you to this interview to satisfy our obligation but I was intensely curious about you after reading your manuscript. Would you mind if I asked you a few questions and that it was recorded?
MS – It would be my pleasure. I am happy to talk about Prophecies.
Ed. – Wonderful. Do you mind if I am blunt?
MS – Not at all. Please.
Ed. – Thank you. Have you published anywhere before? Perhaps under a different name?
MS – No.
Ed. – Remarkable. I was struck, after only a few pages, by your writing. I’ve read the book twice and found it is — although the strangest collection of poetry I have ever read — still an interesting work. It was easy to get caught up in the prophetic format, even for someone as jaded as I am after thirty years in the business. This is no Utopian future here — there are terrible disasters like the end of our own Hilburn Dynasty and the horrific rise and fall of this ‘Tiddan’ Empire. It’s all told in so plausible a voice.
But I would like to know why you decided to publish this book with the title ‘Prophecies’? Isn’t that title dangerous? Some may believe you wish to set yourself up as a Prophet.
MS – Oh I know that no one will think me a prophet at all until long after I’m dead. Not until the prophecies start revealing themselves. You see, between you and I, this isn’t a work of fiction. This book is called ‘Prophecies’ because that is exactly what it contains.
Long Pause.
Ed. – Oh. You believe that.
MS nods.
Ed. – OK — Let’s assume for the moment that these ‘prophecies’ are real. What could motivate you to reveal them in a book? Are you trying to assure your immortality? Or perhaps you want someone to read these and prevent certain events from — um, happening?
MS – I am really not concerned with my immortality in the way you mean it. I know exactly how I will die and, frankly, I look forward to it. The weight of the known future, you see, is incredibly depressing. Nothing major is new for me. Just try to imagine what it would be like if all the ‘news’ was stale for you. Most cannot know how lucky they are not to know the future. But I do know it. I can’t help knowing it.
I will not achieve any kind of fame before I die. But many will read these prophecies: I have seen that this book will become famous. They will even read about this very interview. But few will recognize the truth in any of the passages that may concern them or their time. The handful who may realize it will not be able to alter these events. I’ve seen the future and nothing changes from the course I have plotted in this book even after it is well known.
But your initial question interests me: my motivation. I know what will happen to my family and I clearly see that I will leave no descendants. I didn’t write for them. No one will profit by the book since I have published it myself and the rights will vanish into the public domain before they mean anything. So why publish? It’s part of my particular eccentricity, I suppose. I don’t do it willingly. Not really. I suppose I was compelled to write just as any artist must create. I chose to write in verse because I enjoy the form: the conservation of words, the level of discipline. And the brevity was very much the nature of the revelations themselves. It took over two years and, although it was a struggle at first, once I had several pages done I had as enjoyable a time as I’ve ever had. Hard work, certainly, but worth my time.
Ed. – But if you have already seen everything didn’t you already know the words you would come up with for your book? Why would it take so long?
MS – No you don’t understand. While I knew I would write a book of verse called ‘Prophecies’ I did not know the exact words. Just as while I know what the future holds for countries, certain people and large scale events, I am, at least, blessed with not having to know all the details. I don’t know, for example, how or when you, personally, will die. I’m sure you’re thankful for that.
Laughter.
MS – In any case, I don’t think my head could hold all the details for every future event. I know the ones that matter and of these it was the stories that interested me that I’ve tried to capture in ‘Prophecies’. I felt I owed it to all those who will read this book to do as good a job as I could.
Laughter.
Ed. – But what if you are wrong about the future? Like the passage about how people forget that this is a colony planet with settlers from Earth. Some would consider that preposterous.
MS – I can’t explain why things are going to happen in the way that they will. If it makes you feel better to think that I am suffering from some kind of delusion and that I’ve spent a good deal of my savings on some second or third-rate poetry about a phony future, feel free.
Ed. – I didn’t say anything derogatory about the quality of your poetry.
MS – No matter. Just because I like poetry doesn’t mean I’m good at it. But let’s put aside that this is a future that I’ve convinced myself will come to be. Well then. The writing was a challenging experience and I enjoyed the process. I think it was worth my time. You may not believe that in the future most people will have forgotten that we came here from a distant planet. But that particular stanza doesn’t say that everyone would forget. The truth is that we haven’t heard from Earth or any other planet in over a thousand years: that we are all on a colony world is certainly a fact for us. But we’re also stuck with the reality that we don’t have the metal and likely lack the skill to leave Emily’s World. For most, if not all, of us this is our home. If the silence from other worlds continues, don’t you think it’s possible that many will cease to care where we came from? In any case, I’m stuck with what I see as the future. It was important for me to write down. Call it my future if you like. Whether it’s real or not will be verified, but only well after we’re dead.
Ed. – What are you doing with these 200 copies?
MS – I am giving a few to family and friends but most of them will be going to libraries throughout Hilburn and on the other continents. I have even arranged to have a few stored away in time capsules.
Ed. – Well, prophetic or not, I will say that I did enjoy a good deal of your poetry. Whether it is all the work of your fertile imagination or really the future does not really matter to me. I have taken the liberty of asking for a 201st copy which I have already paid for. I hope you don’t mind and I would be glad to give you extra money for the privilege. I am not usually one for poetry and am certainly not interested in science fiction but I do like and admire this. And I will add that I have edited more self-published books than I could wish of ‘second or third-rate poetry’ as you call it. ‘Prophecies’ is neither. It was certainly much better than I expected from a completely unknown author. If poetry was not so unpopular these days, we might have published this book and paid you.
MS – Thank you Cilla. That really means a great deal to me. And I certainly don’t mind you arranging a 201st copy. In fact I’ll even sign it if you wish. You don’t need to give me anything extra for it. I don’t really need the money.
Ed. – Thank you and you’re welcome. Please return to see me in two weeks and your books should be ready

Note:
I saw in the local news, last week, that Myt-Sentril took his own life. I don’t know what I will do with the above interview. The man seemed sane enough to me. Strange but certainly not suicidal. And the interview strikes me as odd now as it was when I had it recorded over a year ago. I re-read Prophecies over the past few nights and it gave me goose bumps. It’s crazy, I know, but there was something about the man. Perhaps it’s just as well that I keep all this in my private files.
Cilla O-Tourn, March 32, 2244

oh What a zigzag Web has Weaved

On Sunday I went botanizing in the Plant an Old Growth Forest lands with two naturalists. Well. I’m more than a bit rusty so they did most of the botanizing and I held the clipboard and wrote down their discoveries. We started in a wet sedge meadow and found quite a few species and were able to add several species to our list for this natural area.
During our walk I came across an interesting web and its spider. The web was roughly circular but what made it distinct was a thicker zigzag silk in the middle. It looked like a landing pad or a zipper. I was unable to snap a picture of the web or the yellowish brown spider in it so I’m not exactly sure what species it is.
I was, though, able to find that this type of web decoration has a name: stabilimentum. Scientists aren’t sure what purpose these serve but it likely depends on which spider you’re looking at as several species have stumbled on this ‘idea’ over time. If I had to guess the species I saw I’d say it was Argiope aurantia or something very close to it.
The article in Wikipedia suggests that this may have been the inspiration for E.B. White’s Charlotte’s Web which is one of my favourite books. So if the stabilimentum I saw in the sedge meadow I saw was a message I’d have to say that it lead to www or the worldwide web to Wow myself With Web decorations so I would blog about it. How recursive you were you Wonderous Whimsical Web.

Idiots and Electric Bicycles

I’m of two minds when it comes to electric bicycles.

On the one hand they’re really cool because they get you around without polluting too badly depending on where the electricity came from.  But on the other, they’re such a waste. Why spend all that money when you can buy a normal, human-powered bicycle for less and go almost as fast (or even faster downhill) and be burning fat/staying healthy at the same time?

 So it was with disgust that I saw an electric biker towing a bicycle trailer while I was biking home the other day.  This is the kind of trailer that is used to carry kids in behind bicycles.  Sure enough there was a young girl inside.   Neither of them wore bike helmets. The guy had only one hand to steer as he was smoking, holding his cigarette down at the side of his leg allowing the smoke, unfiltered, to flow directly into the little girl’s face.
 
I didn’t know whether to laugh or cry.

Canada Day Events

Guelph has a very active Rotary club who put on many activities and, I presume, do a lot of charity work around town. But their website this year for their big Canada Day Activities event got me laughing yesterday. I wanted to find something that was fun for my kids so I went to their site. Here are the ‘activities’ you get:

• Carousel Rides
• Train Rides
• Pony Rides
• Petting Zoo filled with Goats, Lambs and Bunnies who await love and interactive feeding.
• Mid Way has something for Everyone – Cotton Candy to Thrilling Rides!
• Inflatable Bouncing Games for the Kiddies offer the Giraffe, Castle and Gigantic Slide.
• Fireworks
• Duck Races
• Children’s Play Zone: Children 6 and under a variety of activities
• Rotary Information Tent
• ATM Machine

Again I applaud our Rotarians for what they do (although my kids were in that too old and not old enough range and so didn’t want to go the park with me this year… again) but still, it’s a sad thing when an ATM becomes an activity. Especially when the ‘machine’ is repeated. What’s with that anyway? ATM stands for Automated Teller Machine… why tack on another machine?