## The FizzBuzz Oracle Database Coding Challenge

26 07 2011

July 26, 2011

Through a web search I located a page titled “Coding Horror: Why Can’t Programmers.. Program?”  A simple question was asked in an interview, and apparently 199 of 200 programmers struggled to build a solution for the problem in less than ten minutes.  The problem must be that the 199 people who did not succeed did not have access to an Oracle Database.  The same question was posed to SQL Server developers in the form of a quiz.  Before looking at the articles, see if you are able to solve the following problem with the help of Oracle Database:

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

How many different solutions are there for this problem?  Think about the problem before scrolling down.

My solution:

```SELECT
NVL(DECODE(ROWNUM/3,TRUNC(ROWNUM/3),'Fizz',NULL)||DECODE(ROWNUM/5,TRUNC(ROWNUM/5),'Buzz',NULL),TO_CHAR(ROWNUM)) FIZZBUZZ
FROM
DUAL
CONNECT BY
LEVEL<=100; ```

I think that I remember solving a similar problem using an IBM PC Jr. using BASICA years ago.  How may ways can this problem be solved with the help of Oracle Database?  Before you answer, you might be thinking to yourself why would someone ask such a simple question?  Could there be an overly complex solution, something that the interviewer had never seen before, that was the intended response to the question?

### 25 responses

26 07 2011

some trivial solutions:

```declare
t boolean;
begin
for i in 1..100 loop
t := false;
if mod(i, 3) = 0 then
dbms_output.put('Fizz');
t := true;
end if;

if mod(i, 5) = 0 then
dbms_output.put('Buzz');
t:= true;
end if;

if not t then
dbms_output.put(i);
end if;

dbms_output.new_line;
end loop;
end;

SELECT CASE
WHEN MOD(LEVEL, 15) IN (3, 6, 9, 12) THEN
'Fizz'
WHEN MOD(LEVEL, 15) IN (5, 10) THEN
'Buzz'
WHEN MOD(LEVEL, 15) = 0 THEN
'FizzBuzz'
ELSE
CAST (LEVEL AS VARCHAR2(3))
END
FROM dual
CONNECT BY LEVEL <= 100

```
26 07 2011

Nice start to the list of solutions.

I have another solution to the problem that is a bit different (a bit longer), and I suspect that there are several other solutions to this problem.

In defense of the programmers that were interviewed, I suspect that 198 of the 199 developers spent the 10 minutes trying to determine what programming environment and language should be used for the problem, and then attempting to determine if only the integers between 1 and 100 need to be considered, or if all real numbers need to be output. There was probably only a single programmer that was willing to build something without having the complete specifications. 🙂

As a reminder for people attempting to post code solutions in the comments section, less than (<) and greater than (>) signs tend to cause problem because those are characters with special meaning in HTML. In the comments section:
Less than < — use: & lt; (without the space between the & and the l)
Greater than > — use: & gt; (without the space between the & and the g)
There are several ways to produce monospaced code sections that retain spacing. I typically use <pre> before the code section and </pre> after the code section.

26 07 2011

I like your little puzzles. Soothing after I lose to my wife at sudoku.
If you like to be explicit in following user specifications, then the column below “Fizzbuzz Explicit” more generally follows that path (realizing that the case statement grabs the first true when clause’s then result, so you really don’t need to be quite so tricky), or if you enjoy applying isofunctional axiomatic transforms you can use the least common multiple of 3 and 5 to find values that are commonly divided by 3 and 5 to provide a slightly cheaper solution (though less explicit). The first four columns are just there so beginners can see why it works.

I’ve always wondered how many of the 199 thought they had to write their own mod function. That probably does take more than 10 minutes to do decently in most languages without the library call.

Regards,

mwf

``` select
rownum,
mod(rownum,15),mod(rownum,5),mod(rownum,3),
case
when mod(rownum,5) = 0 and mod(rownum,3) =0 then 'Fizzbuzz'
when mod(rownum,5)  = 0 then 'Buzz'
when mod(rownum,3)  = 0 then 'Fizz'
else to_char(rownum)
end "Fizzbuzz explicit",
case
when mod(rownum,15) = 0 then 'Fizzbuzz'
when mod(rownum,5)  = 0 then 'Buzz'
when mod(rownum,3)  = 0 then 'Fizz'
else to_char(rownum)
end "Fizzbuzz cheaper by lcm"
from sys.obj\$ where rownum < 101;
```
26 07 2011

Mark,

Nice demonstration.

I am starting to wonder if my initial solution to this problem is a little short-sighted. Could it be the case that the 1 programmer that correctly answered the question was the only one without the printed result in the 10 minute time period (disclaimer, I thought of this about a half hour after posting this article)? If we read the interview question again and then think about the following joke, is there possibly a reason why only 1 programmer could correctly answer the question – something that was apparently missed in all of the other articles?

Two programmers walk into two different bars, the mathematician walks between the bars.

26 07 2011

Here’s my PL/SQL example to make it more like a programmer did it.

```declare l_output varchar2(8);
begin
for i in 1..100 loop
l_output := case
when mod(i, 15) = 0 then 'FizzBuzz'
when mod(i, 3) = 0 then 'Fizz'
when mod(i, 5) = 0 then 'Buzz'
else to_char(i) end;
dbms_output.put_line(l_output);
end loop;
end;
/
```
26 07 2011
```select
coalesce(
decode(mod(rownum, 3), 0, 'Fizz')
||decode(mod(rownum, 5), 0, 'Buzz'),
cast(rownum as varchar2(10))
) col2
from dual
connect by level <= 100
```
26 07 2011

T.J Kiernan and Raj, nice examples.

Any other possible solutions? I have two more solutions ready to post to the blog article, cases where efficiency is not the first, second, or even third goal. Now that I think of it, is it possible to create a solution that is simultaneously inefficient and at the same time efficient… I think that I just came up with a third additional solution?

26 07 2011

select decode(mod(level,3),0,’fizz’,null) || decode(mod(level,5),0,’buzz’,null) from dual connect by level < 101;

26 07 2011

Andrew,

That is very close. You just need a small adjustment to satisfy the first part of the requirement:

… that prints the numbers from 1 to 100 …

Your solution completes the hard part, but you might have lost a little bit of your final solution when you posted to the blog article.

I now have 3 other solutions, and I will likely post those in the next 14 hours unless someone posts very similar solutions. The efficient solutions are (fairly) well covered now – how about some inefficient solutions, or efficiently inefficient solutions?

26 07 2011
```create or replace function f_ret_fizzbuzz(p_number in number)
return sys.aq\$_midarray pipelined
as
begin
for i in 1..p_number
loop
pipe row (case when mod(i, 3) = 0 and mod(i, 5) = 0
then 'FizzBuzz'
when mod(i, 3) = 0
then 'Fizz'
when mod(i, 5) = 0
then 'Buzz'
else to_char(i)
end
);
end loop;
return;
end f_ret_fizzbuzz;
/
```
```select *
from table(f_ret_fizzbuzz(100))
/
```
26 07 2011

Raj,

I like that solution. I think that might have been the first SQL related solution on this blog that used a pipelined function.

26 07 2011

This is not one of the three solutions that I am saving. I just thought that I would throw recursion into the mix:

```CREATE OR REPLACE FUNCTION NEXT_NUMBER(START_NUMBER IN NUMBER, MAX_NUMBER IN NUMBER) RETURN VARCHAR2
IS
FIZZBUZZ VARCHAR2(8);
FIZZTEMP VARCHAR2(8);
BEGIN
IF START_NUMBER < MAX_NUMBER THEN
FIZZTEMP := NEXT_NUMBER(START_NUMBER+1, MAX_NUMBER);
ELSE
FIZZTEMP := '100';
END IF;

FIZZBUZZ:=NULL;
IF START_NUMBER/3 = TRUNC(START_NUMBER/3) THEN
FIZZBUZZ:=FIZZBUZZ||'Fizz';
END IF;
IF START_NUMBER/5 = TRUNC(START_NUMBER/5) THEN
FIZZBUZZ:=FIZZBUZZ||'Buzz';
END IF;
FIZZBUZZ:=COALESCE(FIZZBUZZ,TO_CHAR(START_NUMBER));

DBMS_OUTPUT.PUT_LINE(FIZZBUZZ);

RETURN FIZZBUZZ;
END NEXT_NUMBER;
/

CREATE OR REPLACE PROCEDURE START_ME(START_NUMBER IN NUMBER, MAX_NUMBER IN NUMBER)
IS
FIZZBUZZ VARCHAR2(8);
BEGIN
FIZZBUZZ := NEXT_NUMBER(START_NUMBER, MAX_NUMBER);
END;
/

EXEC START_ME(1,100)
```

The output:

```Buzz
Fizz
98
97
Fizz
Buzz
94
Fizz
92
91
FizzBuzz
89
88
Fizz
...
Buzz
4
Fizz
2
1
```

There are cleaner ways of doing the above – I was headed in a couple of different directions when I created the above solution, and I stopped refining the solution when the expected output was achieved.

27 07 2011

Number of different solutions = inf. 🙂

```CREATE OR REPLACE TYPE fizzbuzz_impl AS OBJECT
(
fizzbuzz varchar2(4000),

STATIC FUNCTION odciaggregateinitialize(ctx IN OUT fizzbuzz_impl) RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(SELF  IN OUT fizzbuzz_impl,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT fizzbuzz_impl,
ctx2 IN fizzbuzz_impl) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(SELF        IN OUT fizzbuzz_impl,
returnvalue OUT VARCHAR2,
flags       IN NUMBER) RETURN NUMBER

)
/
```
```CREATE OR REPLACE TYPE BODY fizzbuzz_impl IS

STATIC FUNCTION odciaggregateinitialize(ctx IN OUT fizzbuzz_impl) RETURN NUMBER IS
BEGIN
ctx := fizzbuzz_impl(null);
RETURN odciconst.success;
END odciaggregateinitialize;

MEMBER FUNCTION odciaggregateiterate(SELF  IN OUT fizzbuzz_impl,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN

SELF.fizzbuzz := SELF.fizzbuzz ||
CASE WHEN MOD(CAST(VALUE AS NUMBER), 15) = 0
THEN 'FizzBuzz'
WHEN MOD(CAST(VALUE AS NUMBER), 5) = 0
THEN 'Buzz'
WHEN MOD(CAST(VALUE AS NUMBER), 3) = 0
THEN 'Fizz'
ELSE VALUE
END;

RETURN odciconst.success;
END odciaggregateiterate;

MEMBER FUNCTION odciaggregatemerge(SELF IN OUT fizzbuzz_impl,
ctx2 IN fizzbuzz_impl) RETURN NUMBER IS
BEGIN
SELF.fizzbuzz := SELF.fizzbuzz || ctx2.fizzbuzz;

RETURN odciconst.success;
END odciaggregatemerge;

MEMBER FUNCTION odciaggregateterminate(SELF        IN OUT fizzbuzz_impl,
returnvalue OUT VARCHAR2,
flags       IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := SELF.fizzbuzz;
RETURN odciconst.success;
END odciaggregateterminate;

END;
/

CREATE OR REPLACE FUNCTION
fizzbuzz(x IN VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING fizzbuzz_impl;
/
```
```SELECT fizzbuzz(CAST(LEVEL AS VARCHAR2(10)))
FROM dual
CONNECT BY LEVEL <= 100
GROUP BY LEVEL
;
```
```SELECT fizzbuzz(CAST(LEVEL AS VARCHAR2(10))) OVER (PARTITION BY LEVEL)
FROM dual
CONNECT BY LEVEL <= 100
;
```
27 07 2011

11gr2

```
with fizzbuzz(fb, num) as (
select cast(1 as varchar2(3)), 1 from dual
union all
select case when mod(num+1, 15) = 0 then 'FizzBuzz'
when mod(num+1, 5)  = 0 then 'Buzz'
when mod(num+1, 3)  = 0 then 'Fizz'
else cast(num+1 as varchar2(3))
end fb,
num + 1 num
from fizzbuzz
where num <= 100
)
select fb
from fizzbuzz

```
27 07 2011

Three very nice examples! I will have to spend some time trying to determine how the first 2 of those examples work. It appears that the third example counts to 101, so maybe we should swap the 100 for 99 in that example.

27 07 2011

You’re right, the 100 is not correct, it should be swapped for 99

27 07 2011

```begin
dbms_output.put_line('1
2
Fizz
4
Buzz
Fizz
7
8
Fizz
Buzz
11
Fizz
13
14
FizzBuzz
16
17
Fizz
19
Buzz
Fizz
22
23
Fizz
Buzz
26
Fizz
28
29
FizzBuzz
31
32
Fizz
34
Buzz
Fizz
37
38
Fizz
Buzz
41
Fizz
43
44
FizzBuzz
46
47
Fizz
49
Buzz
Fizz
52
53
Fizz
Buzz
56
Fizz
58
59
FizzBuzz
61
62
Fizz
64
Buzz
Fizz
67
68
Fizz
Buzz
71
Fizz
73
74
FizzBuzz
76
77
Fizz
79
Buzz
Fizz
82
83
Fizz
Buzz
86
Fizz
88
89
FizzBuzz
91
92
Fizz
94
Buzz
Fizz
97
98
Fizz
Buzz');
end;
/
```

😉

27 07 2011

For some reason, I really like that solution. 🙂

27 07 2011

I promised three more solutions to the problem. The first two follow.

The solution that just selects the answers you want, and sorts the result into sequential order technique:

```SELECT
FIZZBUZZ
FROM
(WITH N AS (
SELECT
LEVEL N
FROM
DUAL
CONNECT BY
LEVEL<=100)
SELECT
N.N,
TO_CHAR(N.N) FIZZBUZZ
FROM
N
WHERE
MOD(N,3)>0
AND MOD(N,5)>0
UNION ALL
SELECT
N.N,
'Fizz'
FROM
N
WHERE
MOD(N,3)=0
AND MOD(N,5)>0
UNION ALL
SELECT
N.N,
'Buzz'
FROM
N
WHERE
MOD(N,3)>0
AND MOD(N,5)=0
UNION ALL
SELECT
N.N,
'FizzBuzz'
FROM
N
WHERE
MOD(N,3)=0
AND MOD(N,5)=0)
ORDER BY
N;
```

The “I shall confuse you with WITH blocks that reference each other and then left outer join the result to output what I want you to see”:

```WITH V1 AS
(SELECT
ROWNUM N,
TO_CHAR(ROWNUM) T
FROM
DUAL
CONNECT BY
LEVEL<=100),
V2 AS
(SELECT
ROWNUM*12 N,
'FizzBuzz' T
FROM
DUAL
CONNECT BY
LEVEL<=6),
V3 AS
(SELECT
*
FROM
(SELECT
ROWNUM*3 N,
'Fizz' T
FROM
DUAL
CONNECT BY
LEVEL<=33) V3
WHERE
V3.N NOT IN
(SELECT
N
FROM
V2)),
V4 AS(
SELECT
*
FROM
(SELECT
ROWNUM*5 N,
'Buzz' T
FROM
DUAL
CONNECT BY
LEVEL<=20) V4
WHERE
V4.N NOT IN
(SELECT
N
FROM
V2))
SELECT
COALESCE(V2.T,V3.T,V4.T,V1.T) FIZZBUZZ
FROM
V1,
V2,
V3,
V4
WHERE
V1.N=V2.N(+)
AND V1.N=V3.N(+)
AND V1.N=V4.N(+)
ORDER BY
V1.N;
```
27 07 2011

Hi Charles,

You made me work on my vacation. 😉

Below are three versions from my side (although the CASE is already mentioned in one of the comments above).

— simple CASE

```select level,
case
when mod(level, 3) + mod(level, 5) = 0 then 'FizzBuzz'
when mod(level, 3) = 0 then 'Fizz'
when mod(level, 5) = 0 then 'Buzz'
else to_char(level)
end txt
from dual
connect by level <= 100;
```

```select level,
decode(mod(level, 3) + mod(level, 5), 0, 'FizzBuzz', decode(mod(level, 3), 0, 'Fizz', decode(mod(level, 5), 0, 'Buzz', to_char(level)))) txt
from dual
connect by level <= 100;
```

— simple DECODE

```select level,
Nvl(decode(mod(level, 3), 0, 'Fizz') || decode(mod(level, 5), 0, 'Buzz'), to_char(level)) txt
from dual
connect by level <= 100;
```
27 07 2011

This blog article was made accessible 24 hours ago, so I thought that I would formally share my opinion of why 199 of 200 programmers interviewed simply could not produce the correct answer to the question in less than 10 minutes – I arrived at this conclusion roughly 30 minutes after this blog article was posted. Revisiting my joke shared in a reply to Mark:

Two programmers walk into two different bars, the mathematician walks between the bars.

What? Here is the original specification:

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

Think about that specification – is there sufficient information to produce a “correct” solution? In one of my comments in this article I showed a solution that uses recursion to display the integers from 1 to 100 in reverse numerical order, with the “Fizz” and “Buzz” keywords placed as requested. Did this solution meet the requirements? Maybe? Or should a double quote () have appeared at each end of the keywords? Should the numbers have appeared in numerical order? Should I have restricted the numbers output to just the integers between 1 and 100 (inclusive)? We cannot safely assume that only the integer values should be considered – maybe the whole point of the exercise is to indentify those developers that will produce a solution without fully understanding the desired outcome (I did not see this perspective until it was 30 minutes too late).

A mathematician reading “Write a program that prints the numbers from 1 to 100” might produce a very different solution than a typical programmer. While there are a finite number of integers (whole numbers) between 1 and 100, there are an infinite number of numbers between 1 and 2, between 2 and 3, between 3 and 4, etc. OK, but computers store numbers in a certain precision that eliminates the possibility of an infinite number of number positions to the right of the decimal, so it might be helpful to pick a datatype for the solution. For fun, let’s pick the 32 bit datatype BINARY_FLOAT (4 data bytes plus one length byte). The number 1/3, written in decimal form, has an infinite number of “3” digits to the right of the decimal point. Let’s see how many digits a 32 bit BINARY_FLOAT supports when holding the value of 1/3:

```SET SERVEROUTPUT ON

DECLARE
NUM BINARY_FLOAT;
BEGIN
NUM:=1/3;
DBMS_OUTPUT.PUT_LINE(NUM);
END;
/

3.33333343E-001

PL/SQL procedure successfully completed.
```

It appears that roughly 9 digits to the right of the decimal are supported. Where did that “4” digit come from? Let’s check the documentation:

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric datatypes. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the datatypes FLOAT and DOUBLE in Java and XMLSchema.

OK, so we must be a little careful when using that datatype (if it is good enough for science, no problem?). Let’s try an experiment – we will retrieve all of the numbers with 2 decimal places of accuracy between 1 and 100, and produce the requested output:

```DECLARE
NUM1 BINARY_FLOAT;
NUM2 BINARY_FLOAT;
I BINARY_FLOAT;
FIZZBUZZ VARCHAR2(8);
BEGIN
FOR NUM1 IN 1..99 LOOP
FOR I IN 0..99 LOOP
NUM2:=NUM1 + 0.01 * I;

IF I = 0 THEN
IF NUM2/15 = TRUNC(NUM2/15) THEN
FIZZBUZZ:='FizzBuzz';
ELSE
IF NUM2/3 = TRUNC(NUM2/3) THEN
FIZZBUZZ:='Fizz';
ELSE
IF NUM2/5 = TRUNC(NUM2/5) THEN
FIZZBUZZ:='Buzz';
ELSE
FIZZBUZZ:=NULL;
END IF;
END IF;
END IF;
ELSE
FIZZBUZZ:=NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END LOOP;
END LOOP;

NUM2:=100;
IF NUM2/15 = TRUNC(NUM2/15) THEN
FIZZBUZZ:='FizzBuzz';
ELSE
IF NUM2/3 = TRUNC(NUM2/3) THEN
FIZZBUZZ:='Fizz';
ELSE
IF NUM2/5 = TRUNC(NUM2/5) THEN
FIZZBUZZ:='Buzz';
ELSE
FIZZBUZZ:=NULL;
END IF;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END;
/

99.9000015
99.9100037
99.9199982
99.9300003
99.9400024
99.9499969
99.9599991
99.9700012
99.9800034
99.9899979
Buzz
```

Nice, although we have a couple seemingly random accuracy problems beyond the first digit to the right of the decimal point (but its good enough for science, so says the documentation). You can possibly see why I used nested FOR loops – I wanted to make certain that the numbers that are supposed to be integers actually return as integer values.

Now, extending the previous example, we will retrieve all of the numbers with 7 decimal places of accuracy between 1 and 100, and produce the output that was requested at the start of this blog article:

```DECLARE
NUM1 BINARY_FLOAT;
NUM2 BINARY_FLOAT;
I BINARY_FLOAT;
FIZZBUZZ VARCHAR2(8);
BEGIN
FOR NUM1 IN 1..99 LOOP
FOR I IN 0..9999999 LOOP
NUM2:=NUM1 + 0.0000001 * I;

IF I = 0 THEN
IF NUM2/15 = TRUNC(NUM2/15) THEN
FIZZBUZZ:='FizzBuzz';
ELSE
IF NUM2/3 = TRUNC(NUM2/3) THEN
FIZZBUZZ:='Fizz';
ELSE
IF NUM2/5 = TRUNC(NUM2/5) THEN
FIZZBUZZ:='Buzz';
ELSE
FIZZBUZZ:=NULL;
END IF;
END IF;
END IF;
ELSE
FIZZBUZZ:=NULL;
END IF;
DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END LOOP;
END LOOP;

NUM2:=100;
IF NUM2/15 = TRUNC(NUM2/15) THEN
FIZZBUZZ:='FizzBuzz';
ELSE
IF NUM2/3 = TRUNC(NUM2/3) THEN
FIZZBUZZ:='Fizz';
ELSE
IF NUM2/5 = TRUNC(NUM2/5) THEN
FIZZBUZZ:='Buzz';
ELSE
FIZZBUZZ:=NULL;
END IF;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(COALESCE(FIZZBUZZ,TO_CHAR(NUM2,'990.0000000')));
END;
/
```

Did the output of the above program finish in 10 minutes or less? If Yes, then try the BINARY_DOUBLE datatype and head out to 15 places to the right of the decimal.

Repeating, I do not think that the problem was that 199 of 200 programmers could not solve the problem – I think that the problem was that 199 (or maybe just 150) of the programmers solved a different problem from what was actually requested. 🙂

27 07 2011

Do I win a prize for least efficient method?

```select	actual
,	case 	when mod(actual,15) = 0 then 'FizzBuzz'
when mod(actual,3) = 0 then 'Fizz'
when mod(actual,5) = 0 then 'Buzz'
else	   nvl2(say_thousands,say_thousands||' ',null)
|| nvl2(say_hundreds,say_hundreds||' ',null)
|| filler
|| say_tens
end in_words
from (
select	actual
,	case 	when actual_length = 4 then
thousand_desc||' thousand'
end	say_thousands
,	case 	when hundred_desc is not null then
hundred_desc||' hundred'
end	say_hundreds
,	case	when actual_length >= 3 and last_2_digits > 0 then
'and '
end filler
,	case 	when last_2_digits between 1 and 15 then
actual_desc
when last_2_digits between 16 and 19 then
unit_desc||ten_desc
when last_2_digits between 20 and 99 then
ten_desc||' '||unit_desc
end	say_tens
from (
with bakers as
(select 1 num,'one' dsc from dual union all
select 2,'two' from dual union all
select 3,'three' from dual union all
select 4,'four' from dual union all
select 5,'five' from dual union all
select 6,'six' from dual union all
select 7,'seven' from dual union all
select 8,'eight' from dual union all
select 9,'nine' from dual union all
select 10,'ten' from dual union all
select 11,'eleven' from dual union all
select 12,'twelve' from dual union all
select 13,'thirteen' from dual union all
select 14,'fourteen' from dual union all
select 15,'fifteen' from dual)
, tens as
(select 1 num,'teen' dsc from dual union all
select 2,'twenty' from dual union all
select 3,'thirty' from dual union all
select 4,'fourty' from dual union all
select 5,'fifty' from dual union all
select 6,'sixty' from dual union all
select 7,'seventy' from dual union all
select 8,'eighty' from dual union all
select 9,'ninety' from dual)
, number_stack as
(select	to_char(rownum,'0999') string_number
,	rownum actual
from dual
connect by level <= 9999)
select	stack.actual
,	stack.actual_string
,	stack.last_2_digits
,	bakers1.dsc actual_desc
,	stack.actual_length
,	bakers4.dsc thousand_desc
,	bakers3.dsc hundred_desc
,	stack.hundreds
,	stack.tens
,	tens.dsc ten_desc
,	stack.units
,	bakers2.dsc unit_desc
from (
select 	substr(string_number,length(string_number)-3,1) thousands
,	substr(string_number,length(string_number)-2,1) hundreds
,	substr(string_number,length(string_number)-1,1) tens
,	substr(string_number,length(string_number),1) units
,	actual
,	ltrim(string_number,' 0') actual_string
,	to_number(ltrim(substr(string_number,length(string_number)-1,2),' 0')) last_2_digits
,	length(ltrim(string_number,' 0')) actual_length
from	number_stack
) stack
, bakers bakers1
, bakers bakers2
, bakers bakers3
, bakers bakers4
, tens
where 	bakers1.num (+) = stack.last_2_digits
and	bakers2.num (+) = stack.units
and	bakers3.num (+) = stack.hundreds
and	bakers4.num (+) = stack.thousands
and	tens.num (+) = stack.tens
and 	stack.actual between 1 and 100
)
)
order by actual
/
```
```    ACTUAL IN_WORDS
---------- -----------------
1 one
2 two
3 Fizz
4 four
5 Buzz
6 Fizz
7 seven
8 eight
9 Fizz
10 Buzz
11 eleven
...
96 Fizz
97 ninety seven
98 ninety eight
99 Fizz
100 Buzz
```
27 07 2011

Partially inspired by Neil’s solution (it is interesting, but I am still trying to understand how Neil’s solution works – yes, so far it holds the prize 😉 ):

```WITH N AS
(SELECT
1 N
FROM
ALL_OBJECTS
WHERE
ROWNUM<=10)
SELECT
DECODE(
TRUNC(ROUND(ABS(COS(1.04719733*ROWNUM)),1))+
TRUNC(ROUND(ABS(COS(0.6283184*ROWNUM)),1))*10,
11,'FizzBuzz',1,'Fizz',10,'Buzz',ROWNUM) FIZZBUZZ,
TO_CHAR(TO_DATE(ROWNUM,'J'),'Jsp') FIZZBUZZ2
FROM
N N1,
N N2;

FIZZBUZZ FIZZBUZZ2
-------- ---------
1        One
2        Two
Fizz     Three
4        Four
Buzz     Five
Fizz     Six
7        Seven
8        Eight
Fizz     Nine
Buzz     Ten
11       Eleven
Fizz     Twelve
13       Thirteen
14       Fourteen
FizzBuzz Fifteen
...
Buzz     Ninety-Five
Fizz     Ninety-Six
97       Ninety-Seven
98       Ninety-Eight
Fizz     Ninety-Nine
Buzz     One Hundred
```
2 08 2011

Simple select, NO case, decode, mod :

```select greatest(to_char(level),
replace(instr(level/3,'.'),0,'Fizz'),
replace(instr(level/5,'.'),0,'Buzz'),
replace(instr(level/15,'.'),0,'FizzBuzz')
)
from dual connect by level<=100;
```
2 08 2011

utecistu,

I must admit that I did not see your approach as a possible solution until I tried your SQL statement – the problem description does set itself up rather nicely for alphanumeric sorting. It should be possible to slightly alter your SQL statement to derive another solution using COALESCE rather than GREATEST.