## Sep 22, 2017

Looking around online, I could not find a great example of how to draw Pascal’s Triangle in SQL. The solution is quite cool in SQL, because management studio prints out a nice grid for you. Here are three examples. The first one is simple and well documented, the second one is a little more squished, and the third one is a complete dumpster fire that I got too by looking up how to code golf in SQL.

``````
CREATE PROCEDURE PascalsTriangle
@Levels INT
AS
BEGIN
SET NOCOUNT ON

-- Declare variables
DECLARE @counter        INT = 2
DECLARE @innerCount     INT
DECLARE @tmp            DECIMAL(38,0)
DECLARE @tmp2           DECIMAL(38,0)
DECLARE @dquery         NVARCHAR(2048)

-- Create the results table with its one column
CREATE TABLE #result
(
[1] DECIMAL(38,0)
)
INSERT INTO #result VALUES (1)

-- For every level beyond 1
WHILE(@counter <= @Levels)
BEGIN

-- Add another column to to the table
EXEC('ALTER TABLE #result ADD [' + @counter + '] DECIMAL(38,0)')

-- Select the previous row into a temp table
SELECT *
INTO #row
FROM #result
ORDER BY 1
OFFSET (@counter - 2) ROWS
FETCH NEXT 1 ROWS ONLY

-- Re-initialize the temp variables, and loop counter
SET @tmp = 1
SET @tmp2 = 1
SET @innerCount = 2

-- For every column beyond 1
WHILE(@innerCount <= @counter)
BEGIN

-- Create a query which selects the current value of the cell to back it up.
SET @dquery = 'SELECT @t = [' + CAST(@innerCount AS NVARCHAR(10)) + '] FROM #row'
EXEC sp_executesql @dquery, N'@t DECIMAL(38,0) OUTPUT', @t = @tmp2 OUTPUT

-- Set the value of this cell in this row to that + @tmp
EXEC('UPDATE #row SET [' + @innerCount + '] = ISNULL([' + @innerCount + '], 0) + ' + @tmp)

-- Reset the temp variable
SET @tmp = @tmp2

-- Increment the count
SET @innerCount += 1

END

-- Insert the row into result
INSERT INTO #result
SELECT * FROM #row

-- Increment the counter
SET @counter += 1

-- Drop the row temp table
DROP TABLE #row
END

-- Output the result
SELECT * FROM #result
DROP TABLE #result
END``````

There is another post in PowerShell which does a much better job actually explaining details about how this triangle works. But long story short, every item is the sum of the two above it added together. So now let’s make the script a little smaller.

``````
CREATE PROCEDURE PascalsTriangle2
@Levels INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @o INT=2
DECLARE @ INT
DECLARE @1 DECIMAL(38,0)
DECLARE @2 DECIMAL(38,0)
DECLARE @q NVARCHAR(2048)
CREATE TABLE #o([1] DECIMAL(38,0))
INSERT #o VALUES(1)
o:
SELECT *INTO #r FROM #o ORDER BY 1 OFFSET(@o-2)ROWS	FETCH NEXT 1 ROWS ONLY
SET @1 = 1
SET @2 = 1
SET @ = 2
i:
SET @q = CONCAT('DECLARE @ TABLE (val DECIMAL(38,0));UPDATE #r SET [',@,']=ISNULL([',@,'], 0)+',@1,'OUTPUT deleted.[',@,']INTO @;SET @t=(SELECT *FROM @)')
EXEC sp_executesql @q,N'@t DECIMAL(38,0) OUTPUT',@t=@2 OUTPUT
SET @1 = @2
SET @ += 1
IF @ <= @o GOTO i
INSERT #o
SELECT * FROM #r
SET @o += 1
DROP TABLE #r
IF @o <= @Levels GOTO o
SELECT * FROM #o
DROP TABLE #o
END``````

As you can see, I removed all the pesky comments which were just getting in the way of looking small. Also, the while loops were all turned into GOTO loops to take up a little less space. The variable names were shortened a little as well.

``````
DECLARE @s NVARCHAR(MAX)='CREATE PROCEDURE PascalsTriangle3
@Levels INT
AS
BEGIN
SET NOCOUNT ON
~o |=2~ |~1 &~2 &~q NVARCHAR(2048)CREATE TABLE #o([1] &)INSERT #o VALUES(1)
o:EXEC(''ALTER TABLE #o ADD[''+@o+'']&'')\$|O #r ^#o ORDER BY 1 OFFSET(@o-2)ROWS	FETCH NEXT 1 ROWS ONLY!1 = 1!2 = 1! = 2
i:!q = CONCAT(''~ TABLE (val &);UPDATE #r SET ['',@,'']=ISNULL(['',@,''], 0)+'',@1,''OUTPUT deleted.['',@,'']|O @;!t=(\$^@)'')EXEC sp_executesql @q,N''@t & OUTPUT'',@t=@2 OUTPUT!1 = @2!+=1IF @ <= @o GOTO i
INSERT #o\$ ^#r!o += 1%#r IF @o <= @Levels goto o\$ ^#o%#o
END'
SELECT @s=REPLACE(@s,LEFT(i,1),SUBSTRING(i,2,20))
FROM(VALUES('~ DECLARE @'),('! SET @'),('& DECIMAL(38,0)'),('% DROP TABLE '),('\$ SELECT *'),('^ FROM '),('| INT'))a(i)
EXEC(@s)``````

Now there’s the real beast. It does a bunch of replacements on a string, to make some longer valid SQL, then executes the SQL dynamically to produce the stored procedure. That’s some production quality code.

## Sep 20, 2017

A programming question that I’m sure everyone has been asked at some point during the interview is ‘Pascal’s Triangle’. Besides solving it, nothing would impress your interviewer more than solving it in PowerShell, with support for massively huge numbers. Wikipedia could probably explain this better than I can, but the premise is simple: start with ‘1’, then each row is one element longer than the previous and is made up of the to values above it added together (Except for the values on either end, which are always 1).

``````
function Pascals-Triangle
{
[CmdLetBinding()]
param
(
# Single parameter is the number of levels
[Parameter(Mandatory=\$true)][ValidateRange(1, [int]::MaxValue)][int]\$Levels
)
process
{
# Create a dummy previous array, just so that we know what is going on.
\$prev = [System.Numerics.BigInteger[]]::new(0)

# Now for each level
for(\$l = 0; \$l -lt \$Levels; \$l++)
{
# Create the current working array
\$current = [System.Numerics.BigInteger[]]::new(\$prev.Length + 1)

# We know for sure that the first and the last element are 1 (This avoids bounds checking in the loop below)
\$current[0] = 1
\$current[\$current.Length - 1] = 1

# Now for all other elements add the previous two together
for(\$i = 1; \$i -lt \$current.Length - 1; \$i++)
{
\$current[\$i] = \$prev[\$i - 1] + \$prev[\$i]
}

# Write out our result
Write-Output ([string]::Join(' ', \$current))

# Set the previous to the current for the next iteration
\$prev = \$current
}

}
}
``````

This is most likely the solution that any interviewer would be expecting, because it is by far the simplest to follow. The idea is simple, start with an array of zero length and for every level of the triangle allocate an array which is 1 element longer, and populate it by adding together elements from the previous array. I’m not sure why, but whenever I got this question in college I wanted to do it recursively, which totally screwed me. Also, the reason behind using ‘BigInteger’, is that these number expand very rapidly. Try it out with ‘int’ or even ‘ulong’, and you wont be able to do too many levels. Anyway, the results in powershell end up looking like this.

```1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1```

So now, to mix things up, lets do it in a way that doesn’t require us to keep reallocating arrays. This next solution does the whole thing in place, reusing the same array each time. This is not a big deal, because we always know how long the array needs to be when we start. (Length = Levels). We can just go through and add up the values of the previous row each time.

``````
function Pascals-Triangle2
{
[CmdLetBinding()]
param
(
# Single parameter is the number of levels
[Parameter(Mandatory=\$true)][ValidateRange(1, [int]::MaxValue)][int]\$Levels
)
process
{
# Create our working array
\$row = [System.Numerics.BigInteger[]]::new(\$Levels)

# Now for each level
for(\$l = 1; \$l -le \$Levels; \$l++)
{
# The first index is always 1, and set our previous
\$iPrevious = \$row[0] = 1
for(\$i = 1; \$i -lt \$l; \$i++)
{
# Back up the current value
\$temp = \$row[\$i]

# Add in the previous value
\$row[\$i] += \$iPrevious

# Swap out the previous
\$iPrevious = \$temp
}

# Write out our result
Write-Output ([string]::Join(' ', (\$row | select -First \$l)))
}

}
}
``````

And there we have it. So what about a more obfuscated version for those really hard core interviewees? Lets do it. Taking solution 2 and squishing everything together as much as possible we get a horribly confusing solution.

``````
function Pascals-Triangle3
{
[CmdLetBinding()]
param
(
# Single parameter is the number of levels
[Parameter(Mandatory=\$true)][ValidateRange(1, [int]::MaxValue)][int]\$Levels
)
process
{
(\$r=[System.Numerics.BigInteger[]]::new((\$l=\$Levels)+1))[0]=1
1..\$l|%{"\$(\$r|?{\$_-ne0})";\$t=1;1..\$_|%{\$a=\$r[\$_];\$r[\$_]+=\$t;\$t=\$a}}
}
}``````

Im sure everyone is better at code golf than I am, but this solution already confuses me so it should be a good start.

## Jul 03, 2016

Alright. Its late, and I am bored. Lets say one has a text string, and ones friend is sitting on the other side of the table. One would like them to be able to read this string, but one does not want to turn their computer around, because that would be too much of a hassle. One one would smart to quickly write a program in ones favorite language, Powershell, to flip all of the characters for them. One is not too concerned about efficiency here, because one is watching 'The Transporter'. Actually, it just got to the good part. So one is really hammering out pshell to get this stupid thing to work.

``````
function Flip-Text
{
[CmdLetBinding()]
param
(
[Parameter(Mandatory=\$true, ValueFromPipeline=\$true, Position=0)][String]\$text
)
process
{
\$rightSide='abcdefghijklmnopqrstuvwxyz0123456789&_<>?!()[]{}'
\$upsidedown='ɐqɔpǝɟƃɥıɾʞʃɯuodbɹsʇnʌʍxʎz012Ɛᔭ59Ɫ86⅋‾><¿¡)(][}{'
\$output = ""
for(\$i = \$text.Length -1; \$i -ge 0; \$i--)
{
\$c = \$text[\$i]
\$indR = \$rightSide.IndexOf(\$c)
\$indU = \$upsidedown.IndexOf(\$c)

if(\$indR -gt 0)
{
\$output += \$upsidedown[\$indR]
}
elseif(\$indU -gt 0)
{
\$output += \$rightSide[\$indU]
}
else{
\$output += \$c
}
}

return \$output
}
}``````

So there was the program. All it really does, it maps right side up characters to their inverse. In the spirit of the rest of this site, here is an ugly one liner which does the same thing. Pretty much it's just the first answer all mushed together, but it sure looks cool.

``````
function Flip-Text2
{
[CmdLetBinding()]
param
(
[Parameter(Mandatory=\$true, ValueFromPipeline=\$true, Position=0)][String]\$text
)
process
{
(\$text[(\$text.Length-1)..0]|%{'ɐqɔpǝɟƃɥıɾʞʃɯuodbɹsʇnʌʍxʎz012Ɛᔭ59Ɫ86⅋‾><¿¡)(][}{ '[("\$(97..122|%{[char]\$_})\$(0..9)&_<>?!()[]{}"-replace' ','').IndexOf(\$_)]})-join''
}
}``````