SQL SERVER – T-SQL Script for FizzBuzz Logic

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)

18 thoughts on “SQL SERVER – T-SQL Script for FizzBuzz Logic

  1. 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

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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

    Like

  6. 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;

    Like

  7. 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;

    Like

  8. 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

    Like

  9. 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

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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

    Like

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

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s