Click here to monitor SSC

Simple-Talk columnist

The Joy of Program Chrestomathy

Published 31 January 2014 1:10 pm

After days spent wrestling with an intractable mess of code, with the winter rain beating on the windows, I sometimes find it difficult to maintain my keenness for development work. Is it like this for everyone? You read of cool startups where the California sun streams through the windows and everyone is young and cool, pausing only to make clever changes to open-source projects on GitHub. They plough through life-enhancing programming tasks, doing continuous delivery, group-hugs with DevOps comrades, and other Agile heroics. Sigh.

Of course, even the cynical developer and DBA has guilty pleasures: short breaks from the main task that serve to clear the brain and get the mental juices flowing again.  We get our kicks from forums, scoring points on StackOverflow, answering questions on ASK SQLServerCentral and so on, maybe even writing blogs or articles. Finally, let's not forget program chrestomathy.

A program chrestomathy is a collection of similar programs written in various programming languages, for the purpose of demonstrating differences in syntax, semantics and idioms for each language. Several sites collect program chrestomathies, but I only recently came to appreciate the pleasures of the Rosetta Code Wiki site whilst looking for a good algorithm for calculating Levenshtein distance. I can recommend it. There seems no better way of learning about other programming languages than through comparing the way they tackle common algorithms, puzzles and chores. There are over three hundred different languages represented on the Wiki. To my delight, I found a programming language called Factor, but there was also wonderful, old, hoary Fortran, Ada or Algol code, exotic stuff like Haskell, Lua, M4 or OCaml, and all the mainstream languages such as C#, Java, Javascript or VB. Dialects of C abounded, such C++, as C++/CLI, UC++, Feature C++, Clik++, OpenC++ and Objective C. How great for flexing flabby programming muscles, to rise to the challenge of coding to one of the many tasks that they feature.

Outside a tech interview, you might never need to care about an algorithm, or brainteaser, but here there are over 700 of them. Even so, whilst exploring the site I found many tasks for which TSQL or PowerShell were well suited, but that weren't there, yet!

When browsing the site, I tend to have mixed emotions. Occasionally I find a task over which I’d labored, imagining myself a pioneer, only to find almost identical algorithms already worked out, sometimes, irritatingly, in Cobol or Algol.  Was everything invented thirty or more years ago? I think that program chrestomathy may be growing on me as a furtive pursuit when the February blues get too much.

One of the tasks that I noticed was a routine to take an integer and turn it into a English cardinal number. So 1 becomes ‘One’, -413 becomes ‘minus four hundred and thirteen’ and so on. Some solutions were good, others pathetic, but all amusing.

Here is my TSQL solution to the task. Sure it is going to be slow but it does the job.

IF OBJECT_ID('dbo.To_Written_Numbers') is NOT NULL

     drop function dbo.To_Written_Numbers


CREATE FUNCTION dbo.To_Written_Numbers (@Number INT)


summary:   >

Author: Phil Factor

Revision: 1.0

date: 23rd Oct 2013


     - code: Select dbo.To_Written_Numbers(100)

     - code: Select dbo.To_Written_Numbers(10000)

     - code: Select dbo.To_Written_Numbers(10001)

     - code: Select dbo.To_Written_Numbers(-196)

     - code: Select dbo.To_Written_Numbers(2011)

     - code: Select dbo.To_Written_Numbers(4021)

     - code: Select dbo.To_Written_Numbers(54321)

returns:   >

a written number





  DECLARE @WrittenNumber AS NVARCHAR(200)

  Declare @trail varchar(10)

  DECLARE @EnglishWords TABLE (symbol NVARCHAR(20)

                                  COLLATE SQL_Latin1_General_Cp437_BIN ,

                              DecimalValue INT PRIMARY key,

                              divisor int)


    if @number=0 return 'Zero'

   if @number is null return 'Null'                          


   INSERT  INTO @EnglishWords (symbol, DecimalValue, divisor)

     VALUES('one', 1,0),

          ('two', 2,0 ),

          ('three', 3,0 ),

          ('four', 4,0 ),

          ('five', 5,0 ),

          ('six', 6,0 ),

          ('seven',7,0 ),

          ('eight', 8,0 ),

          ('nine', 9,0 ),

          ('ten', 10,0 ),

          ('eleven', 11,0 ),

          ('twelve', 12,0 ),

          ('thirteen', 13,0 ),

          ('fourteen', 14,0 ),

          ('fifteen', 15,0 ),

          ('sixteen', 16,0 ),

          ('seventeen', 17,0 ),

          ('eighteen', 18,0 ),

          ('nineteen', 19,0 ),

          ('twenty', 20,0 ),

          ('thirty', 30,0 ),

          ('forty', 40,0 ),

          ('fifty', 50,0 ),

          ('sixty', 60,0 ),

          ('seventy', 70,0 ),

          ('eighty', 80,0 ),

          ('ninety', 90,0 ),

          ('hundred', 100,1  ),

          ('thousand', 1000,1  ),      

          ('million', 1000000,1  ),      

          ('Billion', 1000000000,1  )  /*Short scale: Every new term greater than million is a thousand times the previous term. So, billion means a thousand millions, trillion means a thousand billions, and so on */  

  –determine whether a trailing comma or a hyphen is needed. Is it a minus figure?

  Select @trail='', @WrittenNumber = Case when @number<0 then 'minus ' else '' end, @number=abs(@number)      

  WHILE @Number > 0



      @WrittenNumber = case when divisor <>0 then @WrittenNumber +@trail+ dbo.To_Written_Numbers(@Number/decimalValue)+' '+  symbol

                                  else COALESCE(@WrittenNumber, '') + @trail + symbol end,

      @Number = case when divisor <>0 then @Number % DecimalValue else @Number - DecimalValue end,

      @Trail=  case  when divisor <>0 then ', ' else '-' end              


            FROM    @EnglishWords

    WHERE   DecimalValue = (SELECT  MAX(DecimalValue)

                            FROM    @EnglishWords

                            WHERE   DecimalValue <= @number)


if charindex (', ',@WrittenNumber)>0 –replace the last comma with an 'and'

      Select @WrittenNumber=stuff(@WrittenNumber, len(@WrittenNumber)- charindex (' ,',reverse(@WrittenNumber)),

                    2,' and ')


return COALESCE(@WrittenNumber,'NULL')



/* and we do our unit tests. Just a sample of them to show you the principle */

if NOT dbo.To_Written_Numbers (0)='Zero'

  RAISERROR ('failed first test',16,1)

if NOT dbo.To_Written_Numbers(null) = 'Null'

  RAISERROR ('failed second test',16,1)

if NOT dbo.To_Written_Numbers(20000000) = 'Twenty million'

  RAISERROR ('failed third test',16,1)  

if NOT dbo.To_Written_Numbers(2147483647)='two Billion, one hundred and forty-seven million, four hundred and eighty-three thousand, six hundred and forty-seven'

  RAISERROR ('failed fourth test',16,1)  

if NOT dbo.To_Written_Numbers(-76543) = 'minus seventy-six thousand, five hundred and forty-three'

  RAISERROR ('failed fifth test',16,1)  

if NOT dbo.To_Written_Numbers(1001) = 'one thousand and one'

RAISERROR ('failed sixth test',16,1)

if NOT dbo.To_Written_Numbers(-1) = 'minus one'

RAISERROR ('failed seventh test',16,1)

9 Responses to “The Joy of Program Chrestomathy”

  1. Robert Young says:

    – Was everything invented thirty or more years ago?

    You pullin’ my leg??? xml, NoSql, Graph databases, all the rage (more or less) these days were all originally (not that the current kiddies know it) devised before the RM/SQL database, albeit with their original names (finding them is left as an exercise for the reader). Apple claims patent protection for the round cornered rectangle??? So, yes, innovation is dead and buried. We live in a Dark Age where kiddies re-invent square wheels. And are proud of it!!

    All code, whether explicitly programs or implicitly through a DB engine, still run on von Neumann machines. Algorithms, semantics, and syntax are bound by the capabilities of the processor. Any Turing complete one is equivalent to any other, although the translation betwixt them may be difficult, and certain tasks are simpler on one rather than others.

    To the extent that RISC processors do dominate (at the ISA level, not just microarchitecture behind X86 or whatever) going forward, then devising really new application languages becomes more feasible. One can build more kinds of structures from grains of clay than from a pile of two stone cinder blocks.

    Prolog was dead as a doornail during the CISC period, but with RISC machines, making a decent compiler/interpreter isn’t impossible. Without Prolog, Watson likely couldn’t exist.

  2. Robert Young says:

    – Occasionally I find a task over which I’d labored, imagining myself a pioneer, only to find almost identical algorithms already worked out, sometimes, irritatingly, in Cobol or Algol. Was everything invented thirty or more years ago?

    Yes, although grounding the thought in COBOL and Algol puts the distance at 50 years. (COBOL was, to all intents and porpoises, FlowMatic with a bit different syntax.) Not to mention the xml/NoSql/Graph databases are re-inventions of IMS (hierarchy) and IDS/IDMS (network) databases which pre-date both the RM and SQL databases.

    It’s as if each generation actively forgets all that existed before their birth. I suppose that’s a good enough definition of The Dark Ages.

  3. sdmcnitt says:

    Although I have not had to create weekly status reports for some time now, for old time sake I quickly checked out the Rosetta Code site for something in REXX that would generate realistic and usable (fake) status reports. Alas, searching “prose generate” and “sentence creator” and the like yielded no results.

    I found a lot of cool and interesting things but nothing like Foggy, the last fake status report generator I had back in the day.

    Perhaps no one has to generate fake status reports any more —all Agile stand-up meetings?

  4. Phil Factor says:

    So would you like to fake an Agile stand-up?

    • sdmcnitt says:

      @Phil: Nah. Stand-up style of status is preferred over formal, written status reports and a fake “me” would be detected when the ball was passed to me.

      Lets call it the “economics of laziness and punishment”

  5. Keith Rowley says:

    “What has been will be again,
    what has been done will be done again;
    there is nothing new under the sun.” Ecclesiastes 1:9

    Even your observation that there is nothing new is not new lol.

  6. Jeff Rhodes says:

    I agree completely that seeing the same task in different programming languages is a good way to learn. I am in the e-Learning industry and I used to do conference presentations on how to perform various tasks in ToolBook, Flash, JavaScript, and Silverlight. I turned this into a “Programming for e-Learning Developers” book a few years ago: I wouldn’t say it was a best-seller, but it was apparently quite helpful for those who knew one or more of the environments and had to move to another one. Keep up the good work on Simple Talk and RedGate!

  7. Sergio E. says:

    Always is very usefull to code a solution to a problem in two different ways, even more, when you have restrictions it’s more than usefull it’s crucial to have at least another way to solve things.

    My tought was about a project in my near past that has some restrictions, like “stored procedures and views are absolutely forbidden”

  8. paschott says:

    I think the idea has a lot of merit. I know we were trying to come up with the best method to store schedule data similar to the iCal spec in the database. I found the RFC for it, but after that it was quite difficult to figure out how best to design and code for it because there were so many different ways to approach that problem. The basics of “this event happens and has a basic recurrence pattern” isn’t too hard, but the nuances of those patterns and any exceptions to that pattern that arise are a bit more complex.

    I think I’ll spend a bit of time on that site to see what’s available and look at the different ways problems have been solved. Even if it doesn’t help immediately, it can at least give some ideas on how others have approached similar problems in other languages.

    I wouldn’t say that everything has already been programmed, but there’s definitely a vast library of knowledge that we tend to ignore.

    @Sergio, I find that ironic because we’re approaching things from completely the opposite direction now. We’re told to use procs/views/functions exclusively even if the ORM would do the job just fine. :)

Leave a Reply

Blog archive