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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
IF OBJECT_ID('dbo.To_Written_Numbers') is NOT NULL drop function dbo.To_Written_Numbers go CREATE FUNCTION dbo.To_Written_Numbers (@Number INT) /** summary: > Author: Phil Factor Revision: 1.0 date: 23rd Oct 2013 example: - 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 **/ RETURNS NVARCHAR(200) AS Begin 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 begin SELECT @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) end 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') end go /* 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) |
Load comments