RSS

FizzBuzz in T-SQL

01 Sep

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”

————————————————————————————————-

select
number,
case when number%(3*5)=0 then ‘Mostafa Mohamed Elmasry’
when number%5=0 then ‘Elmasry’
when number%3=0 then ‘Mostafa’
else
cast(number as varchar(10))
end as MyName
from
master..spt_values
where
type=’p’ and number between 1 and 100
———————————————————————
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 100
)
SELECT
CASE
WHEN Number % 3 = 0 AND Number % 5 = 0 THEN ‘Mostafa Mohamed Elmasry’
WHEN Number % 3 = 0 THEN ‘Mostafa’
WHEN Number % 5 = 0 THEN ‘Elmasry’
ELSE CONVERT(VARCHAR(3), Number)
END as myname
FROM Numbers
ORDER BY Number
———————————————————————-
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 @COUNTER %(3*5) = 0
SET @OUTPUT = ‘Mostafa Elmasry’
IF @output = ”
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END
———————————————————–
DECLARE @num INT = 100

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

;WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)

SELECT TOP (@num)
Number,
CASE
WHEN Number % 15 = 0 THEN ‘FizzBuzz’
WHEN Number % 3 = 0 THEN ‘Fizz’
WHEN Number % 5 = 0 THEN ‘Buzz’
ELSE CONVERT(VARCHAR(7), Number)
END AS FizzBuzz
INTO #FizzBuzz
FROM Numbers

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

SELECT FizzBuzz FROM #FizzBuzz ORDER BY Number

DROP TABLE #FizzBuzz

 
Leave a comment

Posted by on September 1, 2011 in Script

 

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