Voice of the DBA

Tag: T-SQL

2020 Advent of Code–Day 3
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 3.

Part 1

Day 3 was tough. The explanation isn’t great, at least, I didn’t get it at first. Essentially you have a map, and then you have some slope. The first part has a slope of right 3, down 1. If you move, assuming the upper left is (1,1), the next spot is 2, 4. That’s if you count going down as positive.

Here’s the map:
```
..##.......

#...#...#..

.#....#..#.

..#.#...#.#

.#...##..#.

..#.##.....

.#.#.#....#

.#........#

#.##...#...

#...##....#

.#..#...#.#
```
If I count each space, then there is a period (.) in this (2,4) space. If it’s a tree, then there is a hash (#) there. We are trying to count trees before we hit bottom.

The trick I missed is that the map we’ve given repeats. It repeats to the right as often as needed to get to the bottom.

This is really a coordinate problem, counting each down as we move, and repeating the map. The trick often in a short width here is to do the math to wrap around from the right to left if you run out of room.

In SQL, I used a loop. I didn’t spend a ton of time, but couldn’t see a good way to avoid this as I need this to be readable, and I need this to keep working through the map. Here’s the code:
```
WHILE @currentrow <= @rows
  BEGIN
    SELECT @currentcol += @right
    IF @currentcol > @width 
      SELECT @currentcol = @currentcol - @width
    SELECT @currentrow += @down;
   SELECT @trees = @trees + CASE
       WHEN SUBSTRING(dataval, @currentcol, 1) = '#' THEN 1
       ELSE 0
    end
     FROM day3
     WHERE rowkey = @currentrow
  END

SELECT @trees AS TreeCount
```
I move, count the value if there’s as tree, and then continue moving through the next rows. The WHERE clause orients the rows.

It worked. I go the right answer here.

Part 2

In part 2, this changes to checking a number of sloops and then multiplying the results together. In terms of my code, this is really a repetitive way of running the code again. I could have used different variables and checked multiple slopes at once, but I was busy.

In python, I did something similar. I essentially calculated the next X and Y position, and then looped through the file. Each time the Y matched the current row, I checked for the matching #. If it matched, increment.

Once I matched the correct Y, I incremented X and Y.
February 24, 2021
2020 Advent of Code–Day 2
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 2.

Part 1

This problem is really a string processing issue. We get a long string that needs splitting into different parts. We have two numbers, a digit to check, and a password. In this part, we are looking to see if our check digit appears x number of times.

For T-SQL, we can use Substring and charindex to split things. I had code like this:
```
SELECT
             SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound
           , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound
           , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1)
           , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50)
        FROM dbo.Day2 AS d
```
From here, I can do some counting to determine if we have the correct number of check digits.

For Python and PowerShell, I used split functions. The PowerShell one I did in two routines, one to break the numbers off into $counters, after everything is split.
```
$values = $line.Split(' ')
$counters = $values.split('-')
```
From here, I could then break everything up into the 4 parts.
```
$min = $counters[0]
$max = $counters[1]
$checkvalue = $values[1].Substring(0, $values[1].Length - 1)
$pwd = $values[2]
```
Now I get a count, and then an IF statement that can clean this up.
```
if (($count -ge $min) -and ($count -le $max)) { $part1 += 1}
if (($checkvalue -eq $pwd[$min-1]) -ne ($checkvalue -eq $pwd[$max-1])) { $part2 += 1}
```
This worked right away.

Part 2

In Part 2, rather than counting, we are deciding if the check digit is in the positions specified by the numbers. The trick here is to check if that substring is equal to the digit. Again, in PoSh, the trick is to see if we have one or the other, which is the requirement.
```
if (($checkvalue -eq $pwd[$min-1]) -ne ($checkvalue -eq $pwd[$max-1])) { $part2 += 1}
```
This code looks if the digit matches first, then the second digit matches, and if these are both a 0 or 1, it’s not valid. If only one is valid, we increment the counter.

The python code is very similar.
February 17, 2021
2020 Advent of Code–Day 1
This series looks at the Advent of Code challenges.

I started the Advent of Code at the beginning of December 2020, but life quickly got in the way. Weekends especially, where I try to get away from the computer, so I fell behind. However, I did work through a few, and one of my goals in 2021 is to get through all of them.

I’m going to document my solutions on my blog.

Day 1

The first thing I did was set up a template for the solutions. This is clearly important, and I used some basic ASCII art.

From here, I tackled the challenge. This is one suited for databases, as there is the need to take a list of numbers and find two that add up to 2020. I created a simple table that contained a single column to store numbers.
```
CREATE TABLE Day1
(  datavalue INT)
GO
```
In here I inserted the test data from the challenge.

The easy way for me to tackle this quickly was cross join the numbers with a sum. I put this in a CTE, which gives me the sum of all individual numbers.
```
WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)
```
Once I had this, in the outer query I added a WHERE that limited the results to the sum being equal to 2020, and for the column list, I produced the product.
```
WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)
SELECT a, b, a * b AS solution
FROM cteCalc WHERE sumab = 2020;
go
```
This gave me the result.

As a hint, I used BULK INSERT to load the complete data from the test file into my table.

Part 2

Each challenge has two parts, with the same data. In this one, I had to find 3 entries that summed to 2020. I just added another cross join and this was solved.
```
WITH cteCalc (a, b, c, sumabc)
AS (   SELECT
                       a.datavalue, b.datavalue, c.datavalue,
                       a.datavalue + b.datavalue + c.datavalue AS sumoftthree
        FROM
                       Day1 a
            CROSS JOIN day1 b
            CROSS JOIN day1 c
    )
SELECT a, b, c, a * b * c AS solution
FROM cteCalc WHERE sumabc = 2020;
GO
```
All in all, an easy day. Now I need to solve this in Python.
January 27, 2021
Changing Values in T-SQL–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I ran across a question posted by a beginner on the Internet and thought this would be a good, basic topic to cover. The question was: how can I replace a value in a comma separated string in a table?

This post covers the basics of this task.

Scenario

Suppose you have some strings in a table, and they contain multiple values. I see this often when application developers serialize some data. For example, I might create a table like this:
```
CREATE TABLE mytable
(   mykey INT NOT NULL CONSTRAINT mytablepk PRIMARY KEY
   , myval VARCHAR(100));
GO

INSERT dbo.mytable
     (mykey, myval)
VALUES
     (1, 'apple,pear,banana')
   , (2, 'pear,peach,melon');
GO

SELECT * FROM dbo.mytable AS m;
```
This has a few rows of multiple values in a field.

Imagine now I need to change pear to grape in all rows. I want a simple solution to do this.

Solution

I have seen some people try to use complex substring calls paired with other functions to do this, but T-SQL gives you a really simple solution. We have a REPLACE() function that allows us to change a string without parsing it.

The simple way to do this is like this:
```
SELECT
      m.mykey
    , m.myval
    , REPLACE(m.myval, 'pear', 'grape') AS newstring
FROM dbo.mytable AS m;
```
Always run a SELECT before an UPDATE, but in this case, I can see that pear has been removed and grape is in its place.

REPLACE() works by passing in a string as the first parameter, then a second string to search for, pear in this case, and finally a replacement. I could then put together an UPDATE statement to change my table.
```
UPDATE dbo.mytable
  SET myval = REPLACE(myval, 'pear', 'grape')
FROM dbo.mytable AS m;
```
If I run this, the results shown above for newstring will replace the myval string for all rows.

SQLNewBlogger

This is an example of a basic type of T-SQL solution that is simple, with a quick explanation. I answered this for someone and then spent 10 minutes writing this up.

A good story to have ready for an interview.
January 6, 2021