Category: sql-server

Words: 1,283

Time to read: ~ 7 minutes

Welcome to T-SQL Tuesday, the monthly blogging party where a host provides a topic, and we blog about it.

This month we have Reitse Eskens ( blog | twitter | mastadon ) asking us to talk about something fun we have done with T-SQL.

It has been quite a while since I have coded just for fun, so I’m thankful to Reitse for suggesting this. Unfortunately, I don’t have a pre-baked idea for this T-SQL Tuesday, so let’s see what we can come up with.

Echos

Around December 2021, Wordle hit the virtual scenes. Yeah, nearly two years ago. How do you feel about that?

I got swept up in that wave for a while in the same way I got swept up in the other trends of my time, like Pokemon, Sodoku, and Angry Birds.

Eventually, I stopped when I found a PowerShell script by Kieran Walsh ( github | twitter ) where you could narrow down to the correct answer by putting in the results of your guess each round.

This hack led to me realising how much time I was spending on Wordle and that I should stop, much like I did with Pokemon, Sodoku, and Angry Birds.

So, what better thing to do than to try and recreate that PowerShell script in T-SQL

Rules

I must recreate as much of the script as possible in T-SQL in only one hour.
Yes, I’m aware that’s more of a rule than rules but Wordle needs five letters dammit, and “rule” was crying out for that prosthetic “s”!

Total (code)

Don’t worry, you just have to fill in the variables on lines 19-26.

Split

A few things need to be taken care of out of the bat.

The potential answers have to be stored somewhere in the database. Thankfully, I had the answers in a text file, so creating a table and then inserting them was easy.

I could do the insert with flat files, but I already have PowerShell open so…

$WordleAnswers = Get-Content -Path $HOME\Documents\wordle-answers-alphabetical.txt |
    ForEach-Object -Process {
        [PSCustomObject] @{
            WordleAnswer = $_
        }
    }

$WriteTableParms = @{
    SqlInstance = 'localhost\SQL2019'
    Database = 'Wordle'
    Schema = 'dbo'
    Table = 'WordleAnswers'
    ColumnMap = @{
        "WordleAnswer" = "wordle_answers"
    }
}
$WordleAnswers |
    Write-DbaDataTable @WriteTableParams

Next, we need the variables that we can create. If I can finish this before the 1-hour mark, I’ll turn this into a stored procedure with parameters and everything! Until then, it’s script and variable times.

DECLARE
	@known_letters AS varchar(5),
	@excluded_letters AS varchar(26),
	@position1 AS char(1),
	@position2 AS char(1),
	@position3 AS char(1),
	@position4 AS char(1),
	@position5 AS char(1),
	@correct_letters AS xml,
	@all_answers_sql AS nvarchar(MAX);

/* region Enter Variables here */
SET @known_letters = '';
SET @excluded_letters = '%[]%';

SET @position1 = NULL;
SET @position2 = NULL;
SET @position3 = NULL;
SET @position4 = NULL;
SET @position5 = NULL;
/* endregion Enter Variables here */

The PowerShell code has known_letters, excluded_letters, positions, and wrong_positions.

I can do all these easily enough, except for wrong_positions. I can’t think of a way to do hashtables in SQL that doesn’t equal a secondary table or user-table type, etc. I’ll leave that to the end if I have time.

known_letters is an array of strings. I haven’t updated the SQL Server version on my laptop in a while, so there is no string_split for me. Let’s do the XML way so.

/* region KnownLetters */
SELECT @correct_letters = CONCAT(
	'<known_letters>',
	REPLACE(@known_letters, ',', '</known_letters><known_letters>'),
	'</known_letters>'
);

SELECT
	[known] = [l].[y].value('.', 'char(1)')
INTO #KnownLetters
FROM
(
	VALUES
	(@correct_letters)
) AS [x] ([kl])
CROSS APPLY [kl].nodes('/known_letters') AS [l] (y);
/* endregion KnownLetters */

excluded_letters I can get away with by using some LIKE jiggery-pokery, where it will search for any characters between the square brackets.

positions I can split out into individual variables. I can more easily deal with them then, and it only ends up as an extra five variables this way.

Creating the table would have been handier if I had made a column for each character, but I didn’t, so it’s some SUBSTRING logic for me to get the individual characters out.

SELECT
	[wa].[wordle_answers],
	[g].[char1],
	[g].[char2],
	[g].[char3],
	[g].[char4],
	[g].[char5]
FROM [dbo].[WordleAnswers] AS [wa]
CROSS APPLY (
	VALUES (
		(SUBSTRING([wa].[wordle_answers], 1, 1)),
		(SUBSTRING([wa].[wordle_answers], 2, 1)),
		(SUBSTRING([wa].[wordle_answers], 3, 1)),
		(SUBSTRING([wa].[wordle_answers], 4, 1)),
		(SUBSTRING([wa].[wordle_answers], 5, 1))
	)
) AS [g] ([char1], [char2], [char3], [char4], [char5])

If we do know the positions of some of the letters, then I can strip out a lot of the potential answers straight away. I’m not a fan of Swiss-army-knife WHERE clauses, so I’ll do the dynamic SQL.

I’m also not a fan of WHERE 1=1 in my dynamic code, but I’m running low on time here, and it’s faster to add that in first and start everything else with an AND than it is to check if this is the first clause in the WHERE section or not.

Plus, I’m less against WHERE 1=1 than I am against Swiss-army-knife WHERE clauses.

/* region Known Positions */
CREATE TABLE #AllAnswers
(
	[wordle_answers] char(5),
	[char1] char(1),
	[char2] char(1),
	[char3] char(1),
	[char4] char(1),
	[char5] char(1)
);
SET @all_answers_sql = N'SELECT
	[wa].[wordle_answers],
	[g].[char1],
	[g].[char2],
	[g].[char3],
	[g].[char4],
	[g].[char5]
FROM [dbo].[WordleAnswers] AS [wa]
CROSS APPLY (
	VALUES (
		(SUBSTRING([wa].[wordle_answers], 1, 1)),
		(SUBSTRING([wa].[wordle_answers], 2, 1)),
		(SUBSTRING([wa].[wordle_answers], 3, 1)),
		(SUBSTRING([wa].[wordle_answers], 4, 1)),
		(SUBSTRING([wa].[wordle_answers], 5, 1))
	)
) AS [g] ([char1], [char2], [char3], [char4], [char5])
WHERE 1=1';

IF @position1 IS NOT NULL SET @all_answers_sql = CONCAT(
	@all_answers_sql,
	N'
	AND [g].[char1] = ',
	QUOTENAME(@position1, '''')
);

IF @position2 IS NOT NULL SET @all_answers_sql = CONCAT(
	@all_answers_sql,
	N'
	AND [g].[char2] = ',
	QUOTENAME(@position2, '''')
);

IF @position3 IS NOT NULL SET @all_answers_sql = CONCAT(
	@all_answers_sql,
	N'
	AND [g].[char3] = ',
	QUOTENAME(@position3, '''')
);

IF @position4 IS NOT NULL SET @all_answers_sql = CONCAT(
	@all_answers_sql,
	N'
	AND [g].[char4] = ',
	QUOTENAME(@position4, '''')
);

IF @position5 IS NOT NULL SET @all_answers_sql = CONCAT(
	@all_answers_sql,
	N'
	AND [g].[char5] = ',
	QUOTENAME(@position5, '''')
);

SET @all_answers_sql = CONCAT(@all_answers_sql, N';')

PRINT @all_answers_sql;

INSERT INTO #AllAnswers
EXECUTE [sys].[sp_executesql] @stmt = @all_answers_sql;
/* endregion Known Positions */

Finally, we can UNPIVOT the individual characters for the words and join them with the known_letters to single down to those answers.
As well as excluding characters that we know aren’t in the word.

Or else just return everything we have, minus excluded characters.

IF LEN(@known_letters) > 0 BEGIN
	SELECT
		*
	FROM #AllAnswers AS [w]
	UNPIVOT 
	(
		[chars] FOR [chr2] IN ([w].[char1], [w].[char2], [w].[char3], [w].[char4], [w].[char5])
	) AS [unpvt]
	JOIN #KnownLetters AS [kl]
		ON [unpvt].[chars] = [kl].[known]
	WHERE
		[unpvt].[wordle_answers] NOT LIKE @excluded_letters
END
ELSE
BEGIN
	SELECT
		*
	FROM #AllAnswers AS [a]
	WHERE [a].[wordle_answers] NOT LIKE @excluded_letters;
END;

Guilt

In the PowerShell script, you can add characters in the excluded_letters parameter that exist in the known_letters parameter, and it will correctly ignore them.

Alas, Tempus fugit and I didn’t get to do the same for this T-SQL version. Maybe that has something to do with translating “time flies” into Latin and then looking up other sayings in Latin, but we can’t say for sure. Mea culpa!

However, it’s been around 50 minutes with minor troubleshooting here and there, so time to test this bad boy.

Tests

Let’s hop over to https://wordleplay.com/ and test it out.

I’ll take the first answer returned each time unless it is the answer we chose previously.

PowerShell

I’m not going to use the wrong_positions parameter here since I didn’t re-create that in T-SQL. Still, I got lucky and got the correct answer on the third guess

T-SQL

The T-SQL method doesn’t show each iteration as well as the PowerShell does. And, there’s more human brain power required to make sure you don’t enter the same letter in the known_letters and the excluded_letters variables.

Overall though, well done with a respectable four guesses

Point

I’m not going to say that there is no point to these exercises.

Fun is a valid a point as any other. In a work world filled with more demands on our time than the number of Pokemon (there’s still only 150, right?), more technologies to learn than combinations in Sodoku puzzles, and more people demanding the seemingly impossible out of you so that you want to yeet them at solid objects … something something Angry Birds, it’s a welcome change to do something just for fun once in a while.

Thanks Reitse