XML Feeds
This probably falls under the category of 'Why bother'. However, there has been some recent interest in calculating arbitrarily large fibonacci numbers. There is nothing particularly difficult about calculating these numbers until you reach the maximum number that a bigint (or decimal) can hold. With Fibonacci numbers, you reach this limit pretty fast. There's nothing particularly difficult about calculating this sequence, it merely involves adding 2 numbers together. But, since we run in to the data type limit, we need to start thinking outside the box.
Obviously, we cannot store the numbers in any sort of number data type, so we need to use strings instead. Then, we need to create a way to add strings together as though they were numbers. This is the real challenge with calculating large numbers, but it's really not that difficult either. We'll simply write a function that adds 2 strings together as though they were numbers.
Please understand that this is not production quality code. I'm not validating the inputs and I'm being careless about the data type conversions. Also note that I use the varchar(max) data type. This implies SQL2005 code. If you want to test this on SQL2000, then you'll need to replace varchar(max) with VarChar(8000). You won't be able to calculate as many numbers, but you'll still get quite a few.
- CREATE FUNCTION dbo.AddString(@String1 VARCHAR(8000), @String2 VARCHAR(8000))
- RETURNS VARCHAR(MAX)
- AS
- BEGIN
- DECLARE @CarryTheOne INT
- DECLARE @i INT
- DECLARE @OUTPUT VARCHAR(MAX)
- DECLARE @Digit1 INT
- DECLARE @Digit2 INT
- IF LEN(@String1) < LEN(@String2)
- SELECT @String1 = REPLACE(RIGHT(SPACE(LEN(@String2)) + @String1, LEN(@String2)), ' ', '0')
- ELSE
- SELECT @String2 = REPLACE(RIGHT(SPACE(LEN(@String1)) + @String2, LEN(@String1)), ' ', '0')
- SET @CarryTheOne = 0
- SET @i = 0
- SET @OUTPUT = ''
- WHILE @i < LEN(@String1)
- BEGIN
- SET @Digit1 = SUBSTRING(@String1, LEN(@String1) - @i, 1)
- SET @Digit2 = SUBSTRING(@String2, LEN(@String1) - @i, 1)
- SELECT @OUTPUT = CONVERT(VARCHAR(MAX), (@Digit1 + @Digit2 + @CarryTheOne) % 10) + @OUTPUT
- IF @Digit1 + @Digit2 + @CarryTheOne > 9
- SET @CarryTheOne = 1
- ELSE
- SET @CarryTheOne = 0
- SET @i = @i + 1
- END
- IF @CarryTheOne = 1
- SET @OUTPUT = '1' + @OUTPUT
- RETURN @OUTPUT
- END
The user defined function shown above allows us to add strings as though they were numbers. Now, how do we calculate the fibonacci series? Like this:
- DECLARE @Temp TABLE(Id INT IDENTITY(1,1), FIB VARCHAR(MAX))
- INSERT INTO @Temp(FIB) VALUES('0')
- INSERT INTO @Temp(FIB) VALUES('1')
- DECLARE @i INT
- SELECT @i = MAX(Id) FROM @Temp
- WHILE @i < 1000
- BEGIN
- INSERT INTO @Temp(FIB)
- SELECT dbo.AddString((SELECT Fib FROM @Temp WHERE Id = @i), (SELECT Fib FROM @Temp WHERE Id = @i-1))
- SET @i = @i + 1
- END
- SELECT Fib FROM @Temp ORDER BY Id
That's all there is to it!
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.