Project Euler 008 – TSQL

24 03 2011

Problem 8

Find the greatest product of five consecutive digits in the 1000-digit number.

73167176531330624919225119674426574742355349194934
96983520312774506326239578318016984801869478851843
85861560789112949495459501737958331952853208805511
12540698747158523863050715693290963295227443043557
66896648950445244523161731856403098711121722383113
62229893423380308135336276614282806444486645238749
30358907296290491560440772390713810515859307960866
70172427121883998797908792274921901699720888093776
65727333001053367881220235421809751254540594752243
52584907711670556013604839586446706324415722155397
53697817977846174064955149290862569321978468622482
83972241375657056057490261407972968652414535100474
82166370484403199890008895243450658541227588666881
16427171479924442928230863465674813919123162824586
17866458359124566529476545682848912883142607690042
24219022671055626321111109370544217506941658960408
07198403850962455444362981230987879927244284909188
84580156166097919133875499200524063689912560717606
05886116467109405077541002256983155200055935729725
71636269561882670428252483600823257530420752963450

Solution

declare @digits varchar(MAX)

set @digits =

73167176531330624919225119674426574742355349194934

96983520312774506326239578318016984801869478851843

85861560789112949495459501737958331952853208805511

12540698747158523863050715693290963295227443043557

66896648950445244523161731856403098711121722383113

62229893423380308135336276614282806444486645238749

30358907296290491560440772390713810515859307960866

70172427121883998797908792274921901699720888093776

65727333001053367881220235421809751254540594752243

52584907711670556013604839586446706324415722155397

53697817977846174064955149290862569321978468622482

83972241375657056057490261407972968652414535100474

82166370484403199890008895243450658541227588666881

16427171479924442928230863465674813919123162824586

17866458359124566529476545682848912883142607690042

24219022671055626321111109370544217506941658960408

07198403850962455444362981230987879927244284909188

84580156166097919133875499200524063689912560717606

05886116467109405077541002256983155200055935729725

71636269561882670428252483600823257530420752963450′

 

declare @returnpattern varchar(2);

set @returnpattern = char(13) + char(10)

 

set @digits = replace(@digits, @returnpattern, )

 

declare @currentDigit int

set @currentDigit = 1

declare @sql varchar(max)

 

while(@currentDigit <= len(@digits))

begin

       set @sql = ‘insert into Problem_008 (ordinal, digit) values (‘ + str(@currentdigit) + ‘, ‘ + substring(@digits, @currentDigit, 1) + ‘)’

       exec(@sql)

       set @currentDigit = @currentDigit + 1

end

 

GO

 

select max(product) from

(select

       digit

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 1)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 2)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 3)

       * (select digit from Problem_008 a where a.ordinal = Problem_008.ordinal + 4) as product

from Problem_008

where ordinal < (select count(*) from Problem_008) 3) Products

 

 

Discussion

This one is going to take a bit of explanation.  I parse the thousand digit number for it’s component digits and insert them into a database table with their ordinals.  After the digits are in a table it’s a simple query.

Clearly, I didn’t need to insert the digits into a table to get the result, but as with all of the TSQL solutions I want to solve it as if the information exists in the database if possible.

If you have any questions, leave a comment, find me on Twitter (@azzlsoft) or email (rich@azzlsoft.com).

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: