# Thread: Bit of Friday Fun

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12

## Unanswered: Bit of Friday Fun

A simple problem requiring your most intriguing solution! Code-Golf? NZDF? Pick your poison.

Fizz-Buzz

Return a resultset consisting of the numbers 1 to 100 (inclusive).
If the integer divides by 3 then you should return "fizz".
If the integer divides by 5 then you should return "buzz".
If the integer divides by both 3 and 5 then you should return "fizz-buzz".

i.e.
Code:
```+--------+-----------+
| number | fizz_buzz |
======================
|      1 |           |
|      2 |           |
|      3 | fizz      |
|      4 |           |
|      5 | buzz      |
|      6 | fizz      |
|      7 |           |
|      8 |           |
|      9 | fizz      |
|     10 | buzz      |
|     11 |           |
|     12 | fizz      |
|     13 |           |
|     14 |           |
|     15 | fizz-buzz |
|     16 |           |
+--------+-----------+```
Enjoi!

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I remenbered this with nostalgie.
Five years or more, this was discussed on "Club DB2" forum in Japanese.
The forum was closed now, regretfully.

I thought that it might be necessary two steps to solve the problem.
(1) generate number 1 to 100.
(2) calculate fizz_buzz values for every number generated in (1).

Though, it might be a kind of "reinventing the wheel",
here is my idea which was written and tested on DB2.
(You might want to amend it to conform syntax of Microsoft SQL Server.)
Code:
```WITH
/* (1) generate number 1 o 100. */
numbers
( number ) AS (
VALUES 1
UNION ALL
SELECT number + 1
FROM  numbers
WHERE number < 100
)
/* (2) calculate fizz_buzz values for every number generated in (1). */
SELECT number  AS "number"
, SUBSTR(
CASE MOD(number , 3)
WHEN 0 THEN '-fizz'
ELSE        ''
END
|| CASE MOD(number , 5)
WHEN 0 THEN '-buzz'
ELSE        ''
END
, 2
)  AS "fizz_buzz"
FROM  numbers
;```

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,845
Provided Answers: 16
Bah! Simple:
Code:
```use master
go
exec sp_configure 'show advanced', 1
go
reconfigure with override
go
exec sp_configure 'xp_cmdshell', 1
go
reconfigure with override
go
exec xp_cmdshell 'perl -e ''for (\$i = 1; \$i < 101; \$i++) {!(\$i % 3) && print "Fizz";!(\$i % 5) && print "Buzz";(\$i % 3) && (\$i % 5) && print \$i;print "\n";}'''```

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
I kind of like:
Code:
```; WITH a AS (
SELECT i
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS a(i)
),  d AS (
SELECT 1 + 10 * b.i + c.i AS n
FROM a AS b
CROSS JOIN a AS c
)
SELECT d.n
,  CASE
WHEN 0 = n % 15 THEN 'Fizz-Buzz'
WHEN 0 = n %  5 THEN 'Buzz'
WHEN 0 = n %  3 THEN 'Fizz'
ELSE ''
END AS 'Fizz-Buzz'
FROM d```
-PatP

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
A little shorter code than Pat's.
(Not tested)

Code:
```,  CASE 0
WHEN n % 15 THEN 'Fizz-Buzz'
WHEN n %  5 THEN 'Buzz'
WHEN n %  3 THEN 'Fizz'
ELSE             ''
END AS 'Fizz-Buzz'```
Last edited by tonkuma; 05-03-14 at 15:34.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•