A Crossword Helper in T-SQL: Matching Word Patterns With LIKE

Five letters, second letter A, last letter E, and the answer still refuses to appear. SQL word patterns can narrow that search without guessing every word. The underscore does most of the first job.

Build a Small Word List

Use a disposable database for this helper. The word list contains sample candidates rather than a complete dictionary. Add your own permitted word collection when using it for a real puzzle.

Store one normalized word per row. A primary key prevents duplicate entries under the chosen collation. Keep spaces and punctuation out when the puzzle expects letters alone.

I normalize the list before testing patterns. Mixed formatting creates distracting results. A trailing space shouldn't become an accidental extra puzzle square.

The sample uses a case-insensitive collation. That lets uppercase puzzle input match lowercase stored words. Accent behavior deserves a separate decision when the word list includes accented spelling.

The varchar length bounds this example's words. A broader language collection needs an appropriate Unicode type and collation. The database should preserve the letters your puzzle permits.

CREATE TABLE dbo.CrosswordWordsDemo
(
    WordText varchar(30) COLLATE Latin1_General_100_CI_AS NOT NULL PRIMARY KEY
);
INSERT dbo.CrosswordWordsDemo(WordText) VALUES
('table'), ('cable'), ('dance'), ('eagle'), ('angle'), ('glean'), ('alert'), ('alter'), ('later'), ('plate');

Build Word Patterns with One Underscore per Letter

In LIKE, an underscore matches one character. A percent sign matches an arbitrary sequence, including an empty one. Use underscores when the number of missing letters is known.

The pattern below has exactly five positions. Its second position is a and its last position is e. The other positions each match one character.

Word patterns belong in a parameter when they come from a user. Parameterization keeps the pattern outside executable SQL text. It doesn't remove its wildcard meaning.

If a user expects literal underscores, escaping is a different task. This helper deliberately interprets them as missing letters. Make that input convention visible in the interface.

Don't confuse a pattern search with a general linguistic rule. It matches stored characters under the column's collation. The quality of the word list still determines the quality of the candidates.

DECLARE @Pattern varchar(30) = '_a__e';
SELECT WordText FROM dbo.CrosswordWordsDemo
WHERE WordText LIKE @Pattern
ORDER BY WordText;

Express Letter Positions Directly

SUBSTRING makes a specific position easy to read. Combine it with a length test when the clue has a fixed size. The positions in this example start at one.

LEN ignores trailing spaces for ordinary character strings. That is another reason to normalize the stored words. It shouldn't be responsible for cleaning up the list during every search.

The next query expresses the same positional idea without a compact pattern. That helps when several clue rules are built separately. The SQL remains understandable to a reader reviewing the conditions.

Functions on the word column are residual work for this design. Don't expect every SUBSTRING predicate to become an index seek. The small sample favors clarity rather than pretending to need advanced tuning.

I use this version when the application stores known positions separately. It avoids constructing a pattern incorrectly from several fields. Both approaches still need tests for the first and last positions.

SELECT WordText FROM dbo.CrosswordWordsDemo
WHERE LEN(WordText) = 5
  AND SUBSTRING(WordText, 2, 1) = 'a'
  AND SUBSTRING(WordText, 5, 1) = 'e'
ORDER BY WordText;

Sort Letters to Find Anagrams

Anagrams have the same letters with the same multiplicities. Sorting the letters creates a signature that can be compared. Repeated letters must remain in that signature.

The number source below covers every supported position in this sample column. STRING_AGG assembles sorted individual characters. Ordered STRING_AGG is available in recent SQL Server versions with an appropriate compatibility level.

The query normalizes the supplied letters to lowercase. It then builds the same signature for each candidate word. The length condition removes words with a different number of letters.

The sample input angle illustrates the method. It is sample text, not a claim about an exhaustive puzzle solution. The returned list contains only matching words already stored.

Don't use DISTINCT while extracting letters. That would make two copies of a letter look like one. Anagram matching needs a multiset, not merely a set.

DECLARE @Letters varchar(30) = 'angle';
;WITH Positions AS
(
    SELECT TOP (30) CONVERT(int, ROW_NUMBER() OVER (ORDER BY object_id)) AS PositionNumber
    FROM sys.all_objects
), InputSignature AS
(
    SELECT STRING_AGG(CONVERT(varchar(max), SUBSTRING(LOWER(@Letters), PositionNumber, 1)), '')
           WITHIN GROUP (ORDER BY SUBSTRING(LOWER(@Letters), PositionNumber, 1)) AS SignatureText
    FROM Positions WHERE PositionNumber <= LEN(@Letters)
), CandidateSignatures AS
(
    SELECT w.WordText,
           STRING_AGG(CONVERT(varchar(max), SUBSTRING(w.WordText, p.PositionNumber, 1)), '')
           WITHIN GROUP (ORDER BY SUBSTRING(w.WordText, p.PositionNumber, 1)) AS SignatureText
    FROM dbo.CrosswordWordsDemo AS w
    JOIN Positions AS p ON p.PositionNumber <= LEN(w.WordText)
    WHERE LEN(w.WordText) = LEN(@Letters)
    GROUP BY w.WordText
)
SELECT c.WordText FROM CandidateSignatures AS c
CROSS JOIN InputSignature AS i
WHERE c.SignatureText = i.SignatureText
ORDER BY c.WordText;

Anchor Word Patterns with a Known First Letter

A literal leading prefix gives the index a useful starting point. The primary key already indexes WordText. A pattern beginning with an underscore doesn't provide the same leading boundary.

The following search begins with t. It keeps the five-position pattern and asks the index for a more specific part of the list. Inspect the actual access path on a larger representative collection.

A tiny table still leads SQL Server to scan sometimes. That isn't evidence that the index cannot help a prefix. The optimizer compares the work available for the actual table.

With a leading letter and separate position tests, combine the prefix with those tests. Let the prefix narrow candidates first. The remaining checks then decide which candidates satisfy the clue.

Word patterns are easy to make less selective by accident. A percent sign at the beginning changes the access opportunity. Use it only when the clue permits an unknown prefix length.

SELECT WordText FROM dbo.CrosswordWordsDemo
WHERE WordText LIKE 't____'
ORDER BY WordText;

Keep the Helper Honest About Its List

A clue can have several possible words. Return the candidates rather than choosing one without the clue's meaning. SQL doesn't know why the puzzle setter used that particular wording.

For a large list, precompute anagram signatures during import. Index the signature with the word length. Then an anagram lookup doesn't need to sort every stored word repeatedly.

Rebuild those signatures when normalization rules change. Collation and language choices affect letter comparisons. Store the rule beside the collection rather than hiding it in an old import script.

Which letters are confirmed, and which are assumptions from another answer? Keep that distinction in your input. One confidently wrong crossing letter produces a beautifully empty result.

I test patterns against a few hand-selected words before searching the full list. I also check repeated letters in the anagram test. Word patterns become useful when the stored list and the clue rules agree.

Related reading on this blog: Leading Wildcard LIKE Searches: Why They Scan and What Helps and Puzzle #1: Querying Pattern Ranges and Wild Cards.

A crossword helper is not a source of every answer, it is a way to narrow candidates from known letters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.