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

Reference : **Pinal Dave (****http://blog.SQLAuthority.com****)**

### Like this:

Like Loading...

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

Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

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