Solving FizzBuzz using SQL

This week, my coworkers and I were given a fun challenge. Using any tool or language, solve FizzBuzz! 🤓 Then present and explain the solution to the rest of the team. This was a fun challenge because our team is a mix of junior-to-senior developers and data professionals, working with everything from SQL to Python to C# to DAX to PowerShell. Those who had never solved FizzBuzz before got the chance to do so, while those who had already solved it got the chance to try again using a different tool or language.

While working on my solution, I ended up searching my own website for a post I wrote years ago: Using a Numbers Table in SQL Server. It gave me a nudge to share my FizzBuzz process and solution as well, even if there are a bazillion solutions already out there. I keep telling others to share what they do and learn, so this time I’m actually going to take my own advice. Go me! 😄

The FizzBuzz Challenge

FizzBuzz might be one of the most common programming challenges. The goal is to list all numbers from 1-100, but if a number is divisible by 3 you replace it with Fizz, if a number is divisible by 5 you replace it with Buzz, and if a number is divisible by both 3 and 5 you replace it with FizzBuzz:

1. 1
2. 2
3. Fizz
4. 4
5. Buzz
6. Fizz
7. 7
8. 8
9. Fizz
10. Buzz

…and so on.

Solving FizzBuzz in SQL

My first idea was to simply insert all values into a table and do a SELECT *, but I also wanted to actually solve the challenge 😂

Step 1: List all numbers from 1-100

I came up with three approaches using ROW_NUMBER() to list all numbers from 1-100.

My first approach was to query sys.all_objects:

SELECT TOP (100)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_objects
ORDER BY n;

This felt a little too quick and dirty, though.

Next, I wanted to see if I could list the numbers without querying a table/view. At this point, I had the idea of a numbers table (or tally table) in my mind, since I have used and blogged about it in the past. I just couldn’t remember the syntax! 😅 So I boiled my logic down to “let’s just create 10 x 10 rows”. By using VALUES, I created two virtual tables with 10 rows (each containing the value 1), then cross joined the two tables:

SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM (
  VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
) AS t1(n)
CROSS JOIN (
  VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
) AS t2(n)
ORDER BY n;

Finally, I looked up my numbers table syntax:

WITH
  L0   AS (SELECT 1 AS n UNION ALL SELECT 1),              -- 2 rows
  L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b), -- 4 rows (2 x 2)
  L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b), -- 16 rows (4 x 4)
  L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b), -- 256 rows (16 x 16)
  L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b), -- 65 536 rows (256 x 256)
  L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b), -- 4 294 967 296 rows (65 536 x 65 536)
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (100) 
  n 
FROM Nums 
ORDER BY n;

Step 2: Replace numbers with Fizz, Buzz, or FizzBuzz

Once I had figured out how to list the numbers, I needed to replace the numbers with Fizz, Buzz, and FizzBuzz. The challenge here, in any programming language, is to understand how to check whether a number is divisible by another number, and to understand in which order things are evaluated.

In SQL, you can use the % (modulus) operator to return the remainder after dividing one number by another. If the result is 0, it means that it is divisible.

(Fun fact: The first time I encountered the modulus operator, I thought that it returned the digit(s) after the decimal symbol. As in, I thought that 10 % 4 would return 5 because 10 / 4 = 2.5. Imagine my surprise when it returned 2! Why 2? And then I realized that ohhh, it’s because you can only fully fit the number 4 two times inside 10, and then you have 2 remaining… What can I say, math is difficult enough in my first language! 😅)

The final step is to construct the CASE expression so that it evaluates FizzBuzz first:

WITH
  L0   AS (SELECT 1 AS n UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS n FROM L0 AS a CROSS JOIN L0 AS b),
  L2   AS (SELECT 1 AS n FROM L1 AS a CROSS JOIN L1 AS b),
  L3   AS (SELECT 1 AS n FROM L2 AS a CROSS JOIN L2 AS b),
  L4   AS (SELECT 1 AS n FROM L3 AS a CROSS JOIN L3 AS b),
  L5   AS (SELECT 1 AS n FROM L4 AS a CROSS JOIN L4 AS b),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (100)
  CASE
    WHEN n % 3 = 0 AND n % 5 = 0 THEN 'FizzBuzz'
    WHEN n % 3 = 0 THEN 'Fizz'
    WHEN n % 5 = 0 THEN 'Buzz'
    ELSE CAST(n AS VARCHAR(3))
  END
FROM Nums
ORDER BY n;

Or, if you really just want that quick and dirty solution… 😁

SELECT TOP (100)
  CASE
    WHEN n % 15 = 0 THEN 'FizzBuzz'
    WHEN n % 3 = 0 THEN 'Fizz'
    WHEN n % 5 = 0 THEN 'Buzz'
    ELSE CAST(n AS VARCHAR(3))
  END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS t;

Your Turn!

How would you solve the FizzBuzz challenge? Can you think of a completely different approach? Can you fix my code and make it better or prettier? Go on, do it, it’s fun! 😃

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓