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.
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)
Author: Phil Factor
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)
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,
if @number=0 return 'Zero'
if @number is null return 'Null'
INSERT INTO @EnglishWords (symbol, DecimalValue, divisor)
('two', 2,0 ),
('three', 3,0 ),
('four', 4,0 ),
('five', 5,0 ),
('six', 6,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
WHERE DecimalValue = (SELECT MAX(DecimalValue)
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 ')
/* 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)