Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    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!
    George
    Home | Blog

  2. #2
    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. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #4
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    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 16: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
  •