A Thousand Monkeys? How about a Million Monkeys with T-SQL Code...

As I said yesterday, writing is hard work.  So forget that, let's write stupid code.  It has often been said that a thousand monkeys with a thousand typewriters given enough time would stumble upon the greatest works in literature.  Perhaps.  But have you ever thought about just how unlikely that is?  I did, and wrote some SQL to figure the chances. 

So I wrote this bit of code. 

declare @matchThis varchar(1000)
set     @matchThis = 'a'

select power(cast(27.0 as real),len(@matchThis)) as predictedNumberOfTries

just to calculate it, and then in a minute I will give you the trial and error code behind it.  I do 27 because there are 26 characters + spaces.  We can easily ignore punctuation as well.  So for 'a' the probability is 1 out of 27 that we match.   So what about something like:  'this is the tale of our castaways' which is clearly not Shakespeare?  1 out of 1.717E+47, approximately of course. 

Just to randomly match the four character phrase 'this' is a 1 in 531441 chance.  What follows is the whole code I used.  It only allows a thousand characters, so you can't put in much more than the Gilligan's Island theme, but it does illustrate a very important point.  A thousand monkey's with a thousand typewriters would take millions and millions of years most likely.  Could you flip a coin a million times and always end up with heads?  What if you had a bunch of atoms, do you think a world might just suddenly appear?  I don't want to get off on a rant here, so I will just post the code:

set nocount on
go
if object_id('returnCharacter') is not null
 drop function returnCharacter
go
create function returnCharacter
(
 @value int
)
returns char
as
 begin
 return case when @value = 0 then ' ' else char(96 + @value) end
 end
go
GO

if object_id('testProbability') is not null
 drop table testProbability
go
create table testProbability
(
 testProbabilityId int identity primary key,
 result varchar(1000)
)
go

truncate table testProbability
go

declare @matchThis varchar(1000), @maxtries bigint
set  @maxTries = 1000000
set     @matchThis = 'this'

select power(cast(27.0 as real),len(@matchThis)) as predictedNumberOfTries


declare @numTries bigint, @i int, @keepGoing bit, @holdValue varchar(1000)

set @keepGoing = 1
set @numTries = 1

while @keepGoing = 1
 begin
 set @i = 1
 set @holdValue = ''

 while @i <= len(@matchThis)
  begin  
  select @holdValue = @holdValue + dbo.returnCharacter (cast(rand() * 27 as int))
  set @i = @i + 1
  end

 insert into testProbability (result)
 values (@holdValue)

 if @matchThis = @holdvalue
  begin
  set @keepGoing = 0
  select @numTries
  end

 set @numTries = @numTries + 1

 if @numTries = @maxtries
  begin
  set @keepGoing = 0
  select 'no match within specified max :' + cast(@numTries as varchar(20))
  end

 end

It took 1.5 million attempts to match this.  At least I didn't have to clean up after a thousand monkeys pooping on typewriters for a million years.