TSQL 101 Creating a set of n unique strings

I did this as an exercise in recursive TSQL functions.


with Stuff(one, i) AS (
SELECT CONVERT(NVARCHAR(20), 'one'), 1
UNION ALL
SELECT CONVERT(NVARCHAR(20), 'one' + CONVERT(nvarchar, i)) as one, i + 1
FROM Stuff
WHERE i < 100
)
select one from Stuff 


Which returns: 



one
one1
one2
...
one99


At first, I kept getting this error: 

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "one" of recursive query "Stuff".


So I changed: 
SELECT 'one', 1 
To: 
SELECT CONVERT(NVARCHAR(20), 'one'), 1
in order to match the recursive part's exact type and it worked.

TSQL 101 UNION vs UNION ALL

UNION includes items only once, even if they exist in both sets.
UNION ALL includes items twice if they exist in both sets.

SELECT 'first' UNION
SELECT 'first'

= 'first'

SELECT 'first' UNION ALL
SELECT 'first'

= 'first', 'first'

Neat. This made me wonder if INTERSECT exists in TSQL. To my delight, both INTERSECT and EXCEPT exist.

Fizz Buzz in powershell

For fun, I thought I’d see if I could write Fizz Buzz in powershell.

1 .. 100 | foreach { if ($_ % 15 -eq 0) { Write-Host "FizzBuzz" } elseif ( $_ % 3 -eq 0) { Write-Host "Fizz" } elseif ($_ % 6 -eq 0) { Write-Host "Buzz" } else { Write-Host $_ } }

Or, if you prefer multiple lines:

1 .. 100 | foreach 
{ 
    if ($_ % 15 -eq 0) { 
        Write-Host "FizzBuzz" 
    } elseif ( $_ % 5 -eq 0) { 
        Write-Host "Fizz" 
    } elseif ($_ % 3 -eq 0) { 
        Write-Host "Buzz" 
    } else { 
        Write-Host $_ 
    }
}

Not to hard, but why stop there?

F# Fizz Buzz

[1..100]
|> Seq.map( function     
    | x when x % 15 = 0 -> "BizzBuzz"    
    | x when x % 5 = 0 -> "Bizz"     
    | x when x % 3 = 0 -> "Buzz"     
    | x -> string x ) 
|> Seq.iter(printfn "%s" ) ;;

TSQL Fizz Buzz

My first attempt:

DECLARE @i INT

SELECT @i = 0

WHILE @i < 100

BEGIN

SELECT @i = @i + 1

IF (@i % 15 = 0)

PRINT 'FIZZBUZZ'

ELSE IF (@i % 5 = 0)

PRINT 'FIZZ'

ELSE IF (@i % 3 = 0)

PRINT 'BUZZ'

ELSE

PRINT @i

END 

Not very TSQL-y so I tried again:

DECLARE @i INT

SELECT @i = 0

WHILE @i < 100

BEGIN

SELECT @i = @i + 1

PRINT CASE WHEN (@i % 15 = 0) THEN 'FIZZBUZZ'

WHEN (@i % 5 = 0) THEN 'FIZZ' 

WHEN (@i % 3 = 0) THEN 'BUZZ'

ELSE CAST(@i AS NVARCHAR(MAX))

END

END

A little better, but I found an even better one on the interweb.

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 'FizzBuzz'

    WHEN Number % 3 = 0 THEN 'Fizz'

    WHEN Number % 5 = 0 THEN 'Buzz'

    ELSE CONVERT(VARCHAR(3), Number)

  END

FROM Numbers

ORDER BY Number

This one is full of interesting things. The recursive function at the start is going to take me some time to digest, but it is also very cool.

So once more in javascript:

Javascript Fizz Buzz

for( i = 1; i < 100; i++) {

if (i % 15 == 0) {

console.log("FizzBuzz");

} else if ( i % 3 == 0) {

 console.log( "Fizz" );

} else if ( i % 5 == 0) {

console.log( "Buzz" );

} else {

console.log( i );

}

}

The only thing interesting here is that you can type this in any browser’s javascript console (F12 to open it up).  Other than that, it is boring.