A current frenzy surrounding the game of Wordle has swept the community. I have been playing this game as well and it is addictive. Therefore, I have decided to spice up this game with Transact SQL. With this script, you can now play this game on Microsoft SQL Server, using your favourite editor - SSMS, ADS, VS Code, and others.
the game outlook and concept is based on the original outlook:
About the Game
Wordle is a word guessing game, and your task is simple. You have to guess the searched (or secret) word in six or less tries. The secret word must be taken from the maintained and provided list of words (based on the official dictionary). And this applies also to all the tries. When you pass an incorrect word (or word that does not exist on the dictionary list), you will be informed and this does not count as a try.
The game ends when you find the secret words or when you run out of tries. Along the way, the letters are coloured based on your previous tries. This help you to tinker and play better. Colour coding is straightforward:
- a correctly guessed letter and it's position in the word is coloured green
- a correctly guessed letter but on a wrong position is coloured yellow
- an incorrect letter (a letter that is not part of the word) is coloured gray.
A letter that is correctly guessed (position and letter) turns green, a letter that is correct but placed in wrong wrong position turns yellow and an incorrect letter is coloured as gray.
Getting ready
Before we can start playing the game using T-SQL, there are some prerequisites.
List of words
In order to play Wordle using T-SQL, you will need to import the words. This list of words will serve not only as a corpus of words, but will also set the boundaries. If you import only 20 words, this will mean that you will be limiting yourself to 20 words. In order to get started, I have a curated list of words prepared. You can download the list of words for here. For the English download, use english.txt. In this Github repository, you also have three other languages available.
Create an empty table and import the list of words for the English words.
CREATE TABLE dbo.Words
( ID INT IDENTITY(1,1)
, word NVARCHAR(10) NOT NULL
, lang CHAR(3) NOT NULL
);
-- Insert English words
CREATE TABLE dbo.TempWords
(
word NVARCHAR(10)
);
BULK INSERT dbo.TempWords
FROM 'D:\tsqlwordle\languages\english.txt'
WITH (FIRSTROW = 1
,ROWTERMINATOR='\n');
INSERT INTO dbo.Words
SELECT
word
,'EN' as lang
FROM TempWords;
DROP TABLE IF EXISTS dbo.TempWords;
When you have words for selected language in the SQL Server table, you will also need the procedure for gameplay.
Graphics
Running the game in SQL Server Management Studio will have a slight graphical drawback. The results can not be graphically presented with colours. Therefore, I have created a simple transformation of the colours.
The transformation is the following:
- when a letter (e.g.: C) is correct and the position of the letter is correct, the letter is enclosed in [[ C ]] (denoting green)
- when a letter is correct, but the position is wrong, the letter is represented as {{ C }} (denoting yellow)
- when a letter is incorrect, it is represented as ' C '.
I find this graphical presentation easy to read and quick to remember. You can always change this transformation based on your liking.
Keyboard View
The view of the keyboard will give you additional information about the gameplay. When you are playing the game, the keyboard view will instantly give you an overview of letter selection. If the letter (or the key) is correct and on the correct position, the key will turn [[ C ]] (denoting green). The correct letter in the wrong position will be represented as {{ C }} (denoting yellow) and if the key is incorrect, it will be replaced by the hash (#) sign.
DROP TABLE IF EXISTS dbo.Keyboard;
GO
CREATE TABLE dbo.Keyboard
(
ID INT IDENTITY(1,1)
,Krow INT NOT NULL
,Kkey NVARCHAR(100) NOT NULL
,lang CHAR(3) NOT NULL
)
INSERT INTO dbo.Keyboard
SELECT 1, 'Q; W; E; R; T; Y; U; I; O; P', 'EN' UNION ALL
SELECT 2, 'A; S; D; F; G; H; J; K; L', 'EN' UNION ALL
SELECT 3, 'Z; X; C; V; B; N; M', 'EN' UNION ALL
SELECT 1, 'Q; W; E; R; T; Z; U; I; O; P; Š; Đ', 'SI' UNION ALL
SELECT 2, 'A; S; D; F; G; H; J; K; L; Č; Ć; Ž', 'SI' UNION ALL
SELECT 3, 'Y; X; C; V; B; N; M', 'SI' UNION ALL
SELECT 1, 'Q; W; E; R; T; Z; U; I; O; P; Ü', 'DE' UNION ALL
SELECT 2, 'A; S; D; F; G; H; J; K; L; Ö; Ä', 'DE' UNION ALL
SELECT 3, 'Y; X; C; V; B; N; M', 'DE'
SELECT 1, 'Q; W; E; R; T; Z; U; I; O; P', 'IT' UNION ALL
SELECT 2, 'A; S; D; F; G; H; J; K; L', 'IT' UNION ALL
SELECT 3, 'Y; X; C; V; B; N; M', 'IT'After each try, you will get the keyword view displayed with updated correct, incorrect and unused keys.
Main Game Procedure
The game is played with a single procedure. During the gameplay, the procedure creates two tables to store intermediate results. The procedure also uses the CROSS APPLY, STRING_SPLIT and STRING_AGG clauses and functions to operate and manipulate strings and letters.
The code is shown here:
CREATE OR ALTER PROCEDURE dbo.WordGuess
/*
Script : Game.sql
Procedure : dbo.WordGuess
Purpose : T-SQL stored procedure for playing Wordle in T-SQL
Date Created : 10 January 2022
Description : Popular word game called Wordle in T-SQL
for Microsoft SQL Server 2017+
Based on https://powerlanguage.co.uk/wordle/
Author : Tomaz Kastrun (Twitter: @tomaz_tsql)
(Github: github.com\tomaztk)
Parameters : Two input parameters
@lang -- defines language, thesaurus and keyboard
@guess -- 5-letter word for guessing
Output :
Result of the game:
Table: dbo.TempTable - game play and tries
Table: dbo.TempKeyboard - coloured used keys
Usage:
EXEC dbo.WordGuess
@lang='EN'
,@guess = 'right'
*/ @lang char(3)
,@guess NVARCHAR(10)
AS
BEGIN
-- check if the word exists / is legitt :)
IF (SELECT COUNT(*) as Computed FROM [dbo].[Words] where word = @guess AND lang = @lang) = 0
BEGIN
SELECT 'Wrong word!' AS [Message from the Game]
RETURN
END
-- create table and generate secret
IF (OBJECT_ID(N'dbo.tempTable')) IS NULL
BEGIN
-- DROP TABLE IF EXISTS dbo.tempTable
CREATE TABLE dbo.tempTable (id int identity(0,1), secrets NVARCHAR(10), nof_guess INT, guess_word NVARCHAR(100), valid INT NULL)
DECLARE @secret NVARCHAR(10) = (SELECT top 1 word from dbo.words WHERE lang= @lang ORDER By newid()), INSERT, INTO, dbo.tempTable, (secrets, nof_guess, guess_word, valid), SELECT, @secret, AS, secrets, AS, nof_guess, null, AS, guess_word, 1, AS, valid, --, as, valid, word, END, --, create, table, for, temp, keyboard, IF, (OBJECT_ID(N'dbo.tempKeyboard')), IS, NULL, BEGIN, CREATE, TABLE, dbo.tempKeyboard, (Krow, INT, Kkey, NVARCHAR(100)), INSERT, INTO, dbo.tempKeyboard, (Krow, Kkey), SELECT, --id, Krow, Kkey, FROM, dbo.Keyboard, WHERE, lang, =, @lang, END, --, guessing, part, DECLARE, @nof_guess, INT, =, (SELECT, MAX(nof_guess), FROM, tempTable), IF, @nof_guess, <, 6, BEGIN, /*, ADD, part, for, determing, colours, */, DROP, TABLE, IF, EXISTS, #tt, DECLARE, @guess_sol, NVARCHAR(100), =, '', declare, @guess_sol2, nvarchar(100), =, '', SET, @secret, =, (SELECT, secrets, FROM, dbo.TempTable, WHERE, nof_guess, =, 0), ;WITH, sec, AS, (, SELECT, SUBSTRING(a.b, val.number+1, 1), AS, letter, ROW_NUMBER(), OVER, (ORDER, BY, (SELECT, 1)), AS, RN, FROM, (SELECT, @secret, AS, b), AS, a, JOIN, [master]..spt_values, AS, val, ON, val.number, <, LEN(a.b), WHERE, [type], =, 'P', ), gu, AS, (, SELECT, substring(a.b, val.number+1, 1), AS, letter, row_number(), over, (order, by, (select, 1)), as, RN, FROM, (SELECT, @guess, AS, b), AS, a, JOIN, [master]..spt_values, AS, val, ON, val.number, <, len(a.b), WHERE, [type], =, 'P', ), green, AS, (, SELECT, gu.letter, as, gul, sec.letter, as, secl, gu.rn, as, gurn, FROM, gu, JOIN, sec, ON, gu.rn, =, sec.rn, AND, gu.letter, =, sec.letter, ), yellof, AS, (, select, distinct, g.letter, as, gul, g.rn, as, gurn, from, gu, as, g, cross, join, sec, where, g.letter, =, sec.letter, AND, g.rn, <>, sec.rn, AND, NOT, EXISTS, (Select, *, from, green, as, gg, where, gg.gul, =, g.letter, and, gg.gurn, =, g.rn), ), gray, AS, (, SELECT, letter, as, gul, rn, as, gurn, FROM, gu, WHERE, NOT, EXISTS, (SELECT, *, FROM, green, WHERE, gul, =, gu.letter), AND, NOT, EXISTS, (SELECT, *, FROM, yellof, WHERE, gul, =, gu.letter), ), Aaa, AS, (, SELECT, gul, AS, letter, gurn, AS, pos, 'green', as, col, FROM, green, UNION, ALL, SELECT, gul, AS, letter, gurn, AS, pos, 'yellow', as, col, FROM, yellof, UNION, SELECT, gul, AS, letter, gurn, AS, pos, 'Gray', as, col, FROM, gray, ), final, AS, (, SELECT, a.letter, a.col, CASE, WHEN, a.col, =, 'Gray', THEN, ', '', ', +UPPER(a.letter)+, ', '', ', WHEN, a.col, ='yellow', THEN, ', {{, ', +UPPER(a.letter)+, ', }}, ', WHEN, a.col, ='green', THEN, ', [[, ', +UPPER(a.letter)+, ', ]], ', END, as, reco, a.pos, g.letter, as, guess_letter, FROM, aaa, as, a, LEFT, JOIN, gu, as, g, ON, g.rn, =, a.pos, ), SELECT, *, INTO, #tt, From, final, SELECT, @guess_sol, =, COALESCE(@guess_sol, +, ', ', ''), +, reco, FROM, #tt, ORDER, BY, pos ASC
SELECT @guess_sol2 = COALESCE(@guess_sol2 + ' ,', '') + reco
FROM #tt
ORDER BY pos ASC
-- store results
INSERT INTO dbo.TempTable
SELECT
(SELECT TOP 1 secrets FROM dbo.tempTable) as Secrets
,@nof_guess + 1 aS nof_guess
,@guess_sol
,1 as Valid;
SELECT
nof_guess AS [Try Number:]
,guess_word AS [Guessed Word:]
FROM TempTable
WHERE
ID > 0;
/*
ADD part for keyboard denotation
*/DROP TABLE IF EXISTS #tt2
SELECT
kkey
,krow
,ROW_NUMBER() OVER (ORDER BY krow) AS rn
,TRIM([value]) AS [value]
into #tt2
from dbo.tempkeyboard
CROSS APPLY string_split(kkey, ';')
DROP TABLE IF EXISTS #aa
SELECT
[value]
,TRIM(REPLACE(cast(cast(cast(cast(cast(cast(cast(REPLACE(REPLACE(REPLACE(REPLACE([value] as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)),cast(cast(cast(cast(cast(cast(cast( ' [[ ' as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)),cast(cast(cast(cast(cast(cast(cast('' as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max))))))))))))))), ' ]] ',''),' {{ ',''),' }} ',''), ' '' ','')) AS kak
INTO #aa
FROM STRING_SPLIT(@guess_sol2, ',')
WHERE
[value] <> ''
-- updating values
UPDATE t
SET
t.[value] = a.[value]
FROM #tt2 AS t
JOIN #aa AS a
ON a.kak = t.[value]
UPDATE #tt2
SET
[value] = '#'
WHERE
[value] LIKE ' '''
-- Creating update keyboard outlook
DROP TABLE IF EXISTS dbo.tempKeyboard
SELECT
krow
,STRING_AGG([value], '; ') AS kkey
INTO dbo.tempKeyboard
FROM #tt2
GROUP BY krow
ORDER BY krow asc
-- Output the keyboard
SELECT * FROM dbo.tempKeyboard
END
DECLARE @nof_guess2 INT = (SELECT MAX(nof_guess) FROM dbo.tempTable)
IF @nof_guess2 = 6
BEGIN
SELECT 'End' AS [Message from the Game]
DROP TABLE IF EXISTS dbo.TempTable;
DROP TABLE IF EXISTS dbo.tempKeyboard;
END
IF (UPPER(@secret) = (@guess))
BEGIN
SELECT 'Yees, Won!' AS [Message from the Game]
DROP TABLE IF EXISTS dbo.TempTable;
DROP TABLE IF EXISTS dbo.tempKeyboard;
END
END;
GOPlaying the Game
You will run the game with executing the procedure with two parameters, the language and the guess.
EXEC dbo.WordGuess @lang='EN' ,@guess = 'table'
The language parameter defines the list of words and the guess parameter takes your guess. After each try, you will get the results of all previous results and the keyboard view.
When you guess the correct word, the procedure will let you know that you won.
If your guess is not on the list of words, the procedure will notify you about the false word, and the false try will not count as a try.
Continuing the Wordle-mania
Using transact SQL and playing this word game is not only relaxing, but also gives you the ability to expand the possibilities of T-SQL. And now you can play a simple wordle game during the working hours, without your supervisor knowing, you are playing the game.
Enjoy the Wordle T-SQL game.
