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:tsqlwordlelanguagesenglish.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.comtomaztk)
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
            ,0 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;
GO

Playing 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.