Following is quite common Interview Question asked in many interview questions. FizzBuzz is popular but very simple puzzle and have been very popular to solve. FizzBuzz problem can be attempted in any programming language. Let us attempt it in T-SQL.
Definition of FizzBuzz Puzzle : Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.
DECLARE @counter INT DECLARE @output VARCHAR(8) SET @counter = 1 WHILE @counter < 101 BEGIN SET @output = '' IF @counter % 3 = 0 SET @output = 'Fizz' IF @counter % 5 = 0 SET @output = @output + 'Buzz' IF @output = '' SET @output = @counter PRINT @output SET @counter = @counter + 1 END
What Interviewers Look for in FizzBuzz
This puzzle is not about math. It checks whether a candidate can turn a small set of rules into working code without getting lost. When I ask it, I watch three things: does the loop cover 1 to 100 exactly, does the number 15 print both words, and does the code stay easy to read.
The script above handles the tricky case neatly. It adds Fizz first and then appends Buzz, so a multiple of both gets both words without a separate check. Even the variable size is chosen with care: VARCHAR(8) is exactly long enough for the combined word.
In T-SQL, though, I like to see a candidate who also knows the set based way. A loop works one row at a time, while SQL Server is built to work on sets. Here is the same logic in one query:
SELECT COALESCE(NULLIF(CASE WHEN n % 3 = 0 THEN 'Fizz' ELSE '' END + CASE WHEN n % 5 = 0 THEN 'Buzz' ELSE '' END, ''), CAST(n AS VARCHAR(3))) AS Result FROM (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS t ORDER BY n;
The inner query builds the numbers 1 to 100, and the outer query applies the rules to all of them at once. On SQL Server 2022 you can build the list with GENERATE_SERIES(1, 100) instead.
For 100 rows, speed does not matter at all. What matters is the habit. A developer who reaches for a set based query first will write faster code when the table has ten million rows.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
17 Comments. Leave new
I would take a set-based approach:
CREATE TABLE ##nums
(
number int IDENTITY(1,1),
nothing int null
)
INSERT ##nums (nothing)
SELECT TOP(100) OBJECT_ID FROM sys.all_columns
SELECT ‘FizzBuzz’ =
CASE
WHEN ((number % 3 = 0) AND (number % 5 = 0)) THEN ‘FizzBuzz’
WHEN (number % 5 = 0) THEN ‘Buzz’
WHEN (number % 3 = 0) THEN ‘Fizz’
ELSE CAST(number AS CHAR(3))
END
FROM ##nums
Zod,
Good Script. Let us see what else we get from other exports.
Kind Regards,
Pinal
Following code maybe a little bit dummy but seems more readable?
DECLARE @counter varchar(5)
SET @counter = 1
WHILE @counter < 101
BEGIN
print case
when @counter%3=0 and @counter %5 !=0 then ‘Fizz’
when @counter %3 !=0 and @counter%5=0 then ‘Buzz’
when @counter%3=0 and @counter%5=0 then ‘FizzBuzz’
else @counter end
SET @counter = @counter + 1
END
since we gonna do it in sql server, i suppose the result would be better to display in table:
WITH CTE (Col1)
AS
(
SELECT 1 AS Col1
UNION ALL
SELECT Col1 + 1 FROM CTE
WHERE Col1 < 100
)
SELECT
CASE
WHEN Col1%3 = 0 AND Col1%5 = 0 THEN ‘FizzBuzz’
WHEN Col1%3 = 0 THEN ‘Fizz’
WHEN COl1%5 = 0 THEN ‘Buzz’
ELSE CONVERT(VARCHAR(3), Col1)
END
FROM CTE
declare @v1 varchar(100)
declare @v2 varchar(100)
select @v1=0
while (@v1<100)
begin
select @v1=@v1+1
–select @v2=((@v1%3)=0 and (@v1%5)=0)
if ((@v1%3=0) and (@v1%5=0))
print ‘fizzbuzz’
if(@v1%3)=0
print ‘fizz’
else
if(@v1%5)=0
print ‘buzz’
else
print @v1
end
Declare @num as int
set @num = 1
while @num <= 100
Begin
if(@num%15=0)
print ‘FizzBuzz’
else if(@num%5=0)
print ‘Buzz’
else if(@num%3=0)
print ‘Fizz’
else
print @num
set @num=@num+1;
end
Here’s another qay, very similar to howyue:
WITH
Number
AS
(
SELECT 1 A UNION ALL
SELECT A + 1 FROM Number WHERE A 0 AND A%5 > 0 THEN CAST(A AS VARCHAR) ELSE ” END
FROM
Number;
Brian Tkatch NICE!
Pinal, thanx.
The query did not seem to come through well. Here it is again:
WITH
Number
AS
(
SELECT 1 A UNION ALL
SELECT A + 1 FROM Number WHERE A 0 AND A%5 > 0 THEN CAST(A AS VARCHAR) ELSE ” END
FROM
Number;
I’m not sure if you have any time limit on these posts….
WITH cte (x, y, z) AS
(
SELECT 0 x,
‘ ‘ y,
‘ ‘ z
UNION ALL
SELECT x+1,
CASE WHEN ((x+1)%3 = 0) THEN ‘Fizz’ ELSE NULL END,
CASE WHEN ((x+1)%5 = 0) THEN ‘Buzz’ ELSE NULL END
FROM cte WHERE x 0
Little complicated but again thats also a way
declare @st int
declare @en int
set @st=1
set @en=100
while @st<@en
Begin
if (@st%3=0) AND (@st%5=0 )
begin
Print 'FIZZBUZZ'
end
else
begin
if @st%3=0
begin
PRint 'FIZZ'
end
else
Begin
if @st%5=0
begin
Print 'BUZZ'
end
Else
begin
Print @st
end
End
end
set @st=@st+1
end
with FIZZBUZZ(a ,b)
AS
(
select 1 a,cast(1 as nvarchar(10))b
union all
select a+1 ,
(case when ((a+1)%3)=0 and ((a+1)%5)=0 then ‘FIZZBUZZ’
when ((a+1)%3)=0 then ‘FIZZ’
when ((a+1)%5)=0 then ‘BUZZ’
else cast(a+1 as nvarchar(10))end) as b from FIZZBUZZ
where a<100
)
select * from FIZZBUZZ
Another method
select
case when number%15=0 then ‘FrizzBuzz’
when number%3=0 then ‘Friz’ when number%5=0 then ‘Buzz’
else cast( number as varchar(10)) end
from master..spt_values
where type=’p’ and number>0
Madhivanan
declare @intCount integer =1
, @Buzz varchar(15) = ”
WHILE @intCount <101
BEGIN
SELECT @Buzz = CASE WHEN (@intCount % 3 = 0 AND @intCount % 5 = 0) THEN 'FizzBuzz'
WHEN @intCount % 5 = 0 then 'Buzz'
WHEN @intCount % 3 = 0 THEN 'Fizz'
ELSE convert(varchar(4),@intCount) END
PRINT @Buzz
SET @intCount=@intCount +1
END
A pure SQL solution without a need for loops, etc, which is also pretty portable between different flavours of SQL.
SELECT
CASE
WHEN (a.i + b.i * 10 + 1)% 3 = 0 AND (a.i + b.i * 10 + 1)% 5 = 0 THEN ‘Fizz Buzz’
WHEN (a.i + b.i * 10 + 1)% 3 = 0 THEN ‘Fizz’
WHEN (a.i + b.i * 10 + 1)% 5 = 0 THEN ‘Buzz’
ELSE CONVERT(varchar, (a.i + b.i * 10 + 1 ))
END AS FizzBuzz
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
(SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
ORDER BY a.i + b.i * 10 + 1
DECLARE @i INT
SET @i =1
WHILE @i <= 100
BEGIN
IF ((@i%3 = 0) and (@i%5 = 0))
BEGIN
PRINT 'fizz buzz'
END
ELSE IF(@i%3 = 0)
BEGIN
PRINT 'fizz'
END
ELSE IF (@i%5 = 0)
BEGIN
PRINT 'buzz'
END
ELSE
BEGIN
PRINT @i
END
SET @i = @i +1
END
This was my solution. Surprised I’ve never heard of this until today.
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 100
BEGIN
SELECT
@Counter,
CASE
WHEN @Counter % 3 = 0 AND @Counter % 5 = 0 THEN 'FizzBuzz'
WHEN @Counter % 3 = 0 THEN 'Fizz'
WHEN @Counter % 5 = 0 THEN 'Buzz'
ELSE CAST(@Counter AS CHAR(3)) END AS 'Out'
SET @Counter = @Counter + 1
END