drown? Have you a mind to sink?"
-- Shakespeare, The Tempest
Act I, Scene I, Lines 38-39
The great thing about the FizzBuzz kata is that it is very easy to understand, but has nearly an unlimited number of ways to implement it. I am fan of using the right tool for the job, but I do enjoy stretching things to see what they can and cannot do with the hope of gaining a different point of view. Having said that let us take a look at implementing the FizzBuzz kata in SQL using SQL Server 2012.
Overview of FizzBuzz
The goal of the FizzBuzz kata is to create a list of values which move through a filter with the following rules:
- If the number given is divisible by 3 then produce "Fizz"
- If the number given is divisible by 5 then produce "Buzz"
- If the number is not divisible by either 3 or 5 then produce the number given
FizzBuzz has one edge case:
- If the number given is divisible by both 3 and 5 then produce "FizzBuzz"
An example would be the following:
We see that:
2 => 2
4 => 4
3 => Fizz
9 => Fizz
5 => Buzz
25 => Buzz
15 => FizzBuzz
45 => FizzBuzz
How to Generate the Dataset in SQL Server
Given that we are going to use SQL Server, the first question is how do we generate a dataset to run through our FizzBuzz? We could create a table with all of the values that we need, but that is not very interesting. Instead we will use a Common Table Expression (CTE) to generate our data.
We'll use a Tally Table as outline by Vinay Pugalia's blog post.
DECLARE
@min AS INT = 1
,@max AS INT = 20
;WITH [range] AS (
SELECT @min AS num
UNION ALL
SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT *
FROM [range]
;
This will generate the number 1 to 20 in a dataset.
We can generate any dataset starting at @min up to @max using this CTE, not bad.
Setting up the Business Logic
With our Common Table Expression set up to generate whatever range of numbers we want, we can now focus on the Business Logic of the problem. Since we are looking for particular values to produce a result in some cases it sounds like the perfect job for the CASE statement.
DECLARE
@min AS INT = 1
,@max AS INT = 20
;WITH [range] AS (
SELECT @min AS num
UNION ALL
SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
CASE
WHEN num % 15 = 0 THEN 'FizzBuzz'
WHEN num % 3 = 0 THEN 'Fizz'
WHEN num % 5 = 0 THEN 'Buzz'
ELSE CAST(num AS VARCHAR)
END AS value
FROM [range]
;
Gives use the output we would want:
This gets us what we want, but personally I do not like repeating the logic for divisible by 3 and 5 in the WHEN num % 15 = 0 case.
How about if we join to a set for the Fizz and another set for the Buzz? If we could do that we could use the CONCAT string operation to cover the divisible by 15 edge case.
DECLARE
@min AS INT = 1
,@max AS INT = 20
;WITH [range] AS (
SELECT @min AS num
UNION ALL
SELECT num + 1 FROM [range] WHERE num < @max
)
SELECT
CASE
WHEN CONCAT(fizz.value, buzz.value) <> ''
THEN CONCAT(fizz.value, buzz.value)
ELSE CAST(num AS VARCHAR)
END AS value
FROM [range]
LEFT OUTER JOIN (
SELECT
num AS id
,'Fizz' AS value
FROM [range] WHERE num % 3 = 0
) AS fizz
ON fizz.id = [range].num
LEFT OUTER JOIN (
SELECT
num AS id
,'Buzz' AS value
FROM [range] WHERE num % 5 = 0
) AS buzz
ON buzz.id = [range].num
;
We create subselects which give us the Fizz and Buzz filters we are looking for. We then use the CONCAT to join a Fizz to a Buzz which will give us FizzBuzz on numbers divisible by both 3 and 5. If we do not have a Fizz or a Buzz the CONCAT will give us '' (empty string), which means we'll want to return the number as a string.
This gives us:
We can make this a bit more readable using two more CTEs.
DECLARE
@min AS INT = 1
,@max AS INT = 20
;WITH [range] AS (
SELECT @min AS num
UNION ALL
SELECT num + 1 FROM [range] WHERE num < @max
)
, fizz AS (
SELECT
num AS id
,'Fizz' AS value
FROM [range] WHERE num % 3 = 0
)
, buzz AS (
SELECT
num AS id
,'Buzz' AS value
FROM [range] WHERE num % 5 = 0
)
SELECT
CASE
WHEN CONCAT(fizz.value, buzz.value) <> ''
THEN CONCAT(fizz.value, buzz.value)
ELSE CAST(num AS VARCHAR)
END AS value
FROM [range]
LEFT OUTER JOIN fizz
ON fizz.id = [range].num
LEFT OUTER JOIN buzz
ON buzz.id = [range].num
;
Which still gives us the same output.
Personally I find the three CTEs a bit cleaner to read and understand, but I understand that not everyone will feel the same way.
There you have it FizzBuzz using SQL Server. You can find the solution on SQL Fiddle (and see all my typing errors to get to it).
"They say there's no such place... as Paradise. Even if you search to the ends of the Earth, there's nothing there. No matter how far you walk, it's always the same road. It just goes on and on. But, in spite of that... Why am I so driven to find it? A voice calls to me... It says, 'Search for Paradise.'"
-- Keiko Nobumoto, Wolf's Rain
Final Lines
