I’ve never been much at math
But I’ve always liked reading about it. It’s super interesting, and it always blows my mind. Like, guaranteed. Once in a while I’ll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes I get sick of trying to find new ways to look at DMV data! I stumbled across this Popular Mechanics article recently about five simple math problems that no one can solve. The title is a little misleading, but whatever.
The Collatz Conjecture
The only problem in there that could really be written in SQL was the Collatz Conjecture. It states that when you take any number, and if it’s even you divide it by 2, and if it’s odd you multiply it by 3 and add 1, you’ll always eventually end up with 1. Fair enough. There’s probably a use for that out somewhere out there.
Writing it in SQL was super easy, of course. Just throw a CASE expression at it.
DECLARE @i BIGINT = (SELECT ABS(CHECKSUM(NEWID()) % 9223372036854775807 + 1 /*2305843009213693951*/))
DECLARE @cntr BIGINT = 1
PRINT 'Starting with ' + CONVERT(VARCHAR(100), @i)
WHILE @i <> 1
SELECT @i = CASE
WHEN (@i % 2) = 0
THEN (@i / 2)
ELSE (@i * 3) + 1
PRINT 'Iteration ' + CONVERT(VARCHAR(100), @cntr) + ' resulted in ' + CONVERT(VARCHAR(100), @i)
SET @cntr +=1
I tried throwing some larger numbers at it, but once you get up around the BIGINT max any time you try to multiply by 3 you end up with errors. Even dividing 9223372036854775807 by 3 got me more arithmetic overflow errors than successful tries.
I know, I know. It’s not set-based. Shame on me. But it still runs pretty darn fast, even when you get up to higher numbers. The most steps it took me to get to 1 was 723, and even that ran in milliseconds.
Maybe SQL is alright for math after all!
Thanks for reading!