SQLServerCentral Article

Playing popular game of Wordle using T-SQL

,

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:

wordle_gameplay.png
Gameplay with original Wordle

 

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;

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating