Skip to content

Instantly share code, notes, and snippets.

@shaneis
Created April 11, 2023 21:32
Show Gist options
  • Save shaneis/42b20977394790be4136046e1cf79ef1 to your computer and use it in GitHub Desktop.
Save shaneis/42b20977394790be4136046e1cf79ef1 to your computer and use it in GitHub Desktop.
USE [Wordle];
GO
DROP TABLE IF EXISTS #KnownLetters, #AllAnswers;
GO
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 */
SET @known_letters = LOWER(@known_letters);
SET @excluded_letters = LOWER(@excluded_letters);
SET @position1 = LOWER(@position1);
SET @position2 = LOWER(@position2);
SET @position3 = LOWER(@position3);
SET @position4 = LOWER(@position4);
SET @position5 = LOWER(@position5);
/* 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 */
/* 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 */
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment