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.