Researching An Algorythm For A Word Game (Solved)

[RichardN]

Further to the discussion here: Anagram solving by Regex or SQL I have been presented with a new challenge....

A word game presents you with 9 letters at random. When I say random, the balance between vowels and consonants is selected by the player so as to offer the best chance of making the longest word. The vowels/consonants chosen are generated at random and might be duplicated. Each letters may only be used once, and the player finding the longest word is the winner! Those of you familiar with 'Countdown' or 'Des Chiffres Et Des Lettres' will already understand the game.

I am employing an SQL database that is already populated with c350k words and the 'Signature' of each word that was previously generated for solving anagrams, for example: 'abandoned' has a signature of 'a2b1d2e1n2o1'.

Making the first pass searching for 9 letter matches is really easy as you generate a 'Signature' from the 9 letters and look for matches. Thereafter it starts to get complicated. If you don't find any 9 letter words you have to start excluding letters from the signature in a manner that covers all possible permutations. Let's say I am not interested in 4 letter words so I need to scan all possibilities from 9 down to 5 letters.

What is the most logical method?
For 8 letters I carry out 8 SQL SELECT passes excluding a single character from the signature in sequence 1-9.
For 7 letter words I need to exclude 2 letters at each SELECT pass..... So 1+2, 1+3, 1+4.... until 1+9. The 2+3, 2+4 etc....

As you can see by the time you get to 5 letters there has been an awful lot of SQL activity and I am starting to think there must be a much better way of doing this!

Any suggestions?

 

[emexes]

Have a column for each letter, being an integer count of that letter in the word, from 0 to 9? or just one column with string of 26 digits?

One SQL SELECT will do it, albeit it will have up to 9 letter count filter terms, one for each unique selection letter.

Or 26 terms, if you work down from the top end too, eg if you have 4 selection letters b a i j then each of those letters is NumLetter >= 1 And NumLetter <= 6, and each of the other 22 letters is NumLetter >= 0 and NumLetter <= 5.

 

[emexes]

Hang on, can't SQL do substring matches? In which case, a single query with a clause for each unique letter in the selection letters to check that the letter is contained within the list word, should filter it down to the point just the actual possibilities, perhaps with some extraneous close-but-no-cigar hits caused by duplicate selection letters.

Hmm.

1/ create another signature column, which is just all letters of the word, but sorted eg 'abandoned' has a signature of 'aabddenno'

2/ seach by similarly-grouped selection letters, eg if the search letters are every second letter ie a a d n d, sort and group that to aa dd n, and do a SELECT for signatures that contain all of those letter groups, probably something like:

SELECT * FROM mytable
WHERE signature LIKE '%aa%'
  AND signature LIKE '%dd%'
  AND signature LIKE '%n%'

 

[RichardN]

@emexes ..... I don't think your first post will work as Word LIKE '%a%' also returns all instances of 'aa' in a word as well as 'a'. To avoid that, the query has to be written so the length of the results returned equal the length of the clue submitted.

You are onto something with a simple alphabetic signature. However, as I said above, to submit multiple SQL queries to cover every possible combination is cumbersome and code heavy. If there is some way of avoiding that I await with interest.

 

[emexes]

I don't think your first post will work as Word LIKE '%a%' also returns all instances of 'aa' in a word as well as 'a'.

I'm always happy to be proven wrong, but here I feel we were thinking along parallel but not-quite-the-same tracks.

I understood the problem puzzle challenge to be to find all words that have at least all of the search letters in them. So searching for words with letters a p e in them would return words like apple.

But if you wanted to specify exact, or minimum, numbers of letters, then delimit the signature and search letter groups, eg with /

eg using your example word abandoned, signature would be /aa/b/dd/e/nn/o/
and then searching for /a/ would only match words with a single letter a,
and not match words with more than one letter a like abandoned

eg using my example word apple, signature would be /a/e/l/pp/
and then searching for /p/ would only match words with a single p,
and not match words with more than one letter p like apple

also with delimited signature letter groups, now:

searching for /p/ will match words with exactly one p
searching for p will match words with one or more p's
searching for /pp/ will match words with exactly two p's
searching for pp will match words with two or more p's
searching for /ppp/ will match words with exactly three p's
searching for ppp will match words with three or more p's

 

[William Lancee]

My 2 cents:

Have 26 columns corresponding to the letters of the alphabet (as @emexes suggested). Each column will have the count of that letter in a legal word (0 to 9).
Given a hint of 9 letters... put the count of these letters into a 26 item vector similar to the legal word signatures.

Any word that has letters not in the hint is not valid.
Therefore if the counts in the 26-item words vector are greater than the counts in the 26-item hint vector, the word is not a valid word.

I am pretty sure it is possible to create a one pass query (albeit lengthy) that could do that task
(It can be done in a native B4X code without SQL)

 

[emexes]

if the counts in the 26-item words vector are greater than the counts in the 26-item hint vector, the word is not a valid word.

Hmm, I think you're right: I've been approaching this from the wrong direction, and now I understand this:

I don't think your first post will work

but if we make the SQL condition come at it from the wrong (!) direction too, then I think I can clutch victory from the jaws of defeat:

eg, for hint signature /aa/ee/ll/pp/ then this query

SELECT * FROM mytable
WHERE signature NOT LIKE '%aaa%'
  AND signature NOT LIKE '%b%'
  AND signature NOT LIKE '%c%'
  AND signature NOT LIKE '%d%'
  AND signature NOT LIKE '%eee%'
  AND signature NOT LIKE '%f%'
  AND signature NOT LIKE '%g%'
  AND signature NOT LIKE '%h%'
  AND signature NOT LIKE '%i%'
  AND signature NOT LIKE '%j%'
  AND signature NOT LIKE '%k%'
  AND signature NOT LIKE '%lll%'
  AND signature NOT LIKE '%m%'
  AND signature NOT LIKE '%n%'
  AND signature NOT LIKE '%o%'
  AND signature NOT LIKE '%ppp%'
  AND signature NOT LIKE '%q%'
  AND signature NOT LIKE '%r%'
  AND signature NOT LIKE '%s%'
  AND signature NOT LIKE '%t%'
  AND signature NOT LIKE '%u%'
  AND signature NOT LIKE '%v%'
  AND signature NOT LIKE '%w%'
  AND signature NOT LIKE '%x%'
  AND signature NOT LIKE '%y%'
  AND signature NOT LIKE '%z%'

should find all words able to be constructed from those 8 hint letters, including words shorter than 8 letters long.

I hope.

 

[William Lancee]

And this works because the signature is /aaa/ instead of a3, and /aaa/ matches /aaaa/
Next time I am about to be defeated I'll be searching for you.

 

[emexes]

And this works because the signature is /aaa/ instead of a3

in fact I don't think needs delimiters, but I figured best leave them in in case ever want to search for exact letter count

Next time I am about to be defeated I'll be searching for you.

united we stand...

 

[William Lancee]

No with delimiters it doesn't match. But I was thinking %aaa% matches %aaaa%

 

[emexes]

in case ever want to search for exact letter count

although this might achieve same without needing delimiters:

SELECT * FROM mytable
WHERE signature LIKE '%aa%' AND signature NOT LIKE '%aaa%'

ie here matches words with exactly two letter a's

 

[zed]

Try this. This might interest you.

The SQLite db is too big to attach.
Create a word table like this.

CREATE TABLE "lexique" (
    "Lex_ID"    INTEGER,
    "Ortho"    TEXT,
    PRIMARY KEY("Lex_ID")
)

Copy the full code and Run.

#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=Project.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private db As SQL
   
    Private Label1 As Label
    Private myWORD As String
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
   
    If Not(File.Exists(File.DirApp, "lex.db3")) Then
        File.Copy(File.DirAssets, "lex.db3", File.DirApp, "lex.db3")
    End If
    db.InitializeSQLite(File.DirApp, "lex.db3", True)
   
    myWORD = "abaca"
    Dim matches As List
    matches.Initialize
    ' Reduce the length of the word until a match is found or the word is empty
    Do While myWORD.Length > 0
        LogColor(myWORD.Length,xui.Color_Cyan)
       
        Dim permutations As Map = GenerateUniquePermutations(myWORD)
        InsertPermutationsIntoDB(permutations)
       
        matches = FindMatchesInDB
        myWORD = myWORD.SubString2(0, myWORD.Length - 1) ' Reduce word length
    Loop
   
    For Each match As String In matches
        Log("Match found : " & match)
    Next
   

End Sub

' Generate all unique permutations of a word
Sub GenerateUniquePermutations(s As String) As Map
    Dim res As Map
    res.Initialize
    If s.Length = 1 Then
        res.Put(s, True)
        Return res
    End If
    For i = 0 To s.Length - 1
        Dim ch As String = s.CharAt(i)
        Dim rem As String = s.SubString2(0, i) & s.SubString(i + 1)
        Dim perms As Map = GenerateUniquePermutations(rem)
        For Each perm As String In perms.Keys
            res.Put(ch & perm, True)
        Next
    Next
    Return res
End Sub

' Insert permutations into database
Sub InsertPermutationsIntoDB(permutations As Map)
    db.ExecNonQuery("CREATE TABLE IF NOT EXISTS temp_permutations (word TEXT)")
    db.ExecNonQuery("DELETE FROM temp_permutations;")
    For Each perm As String In permutations.Keys
        Log("Inserting the permutation : " & perm &" - "&myWORD.Length)
        db.ExecNonQuery2("INSERT INTO temp_permutations (word) VALUES (?)", Array As Object(perm))
    Next
End Sub

' Find matches in the database
Sub FindMatchesInDB As List
    Dim matches As List
    matches.Initialize
    Dim RS As ResultSet = db.ExecQuery("SELECT lexique.Ortho FROM lexique JOIN temp_permutations ON lexique.Ortho = temp_permutations.word")
    Do While RS.NextRow
        Dim word As String = RS.GetString("Ortho")
        Log("Word found : " & word)
        matches.Add(word)
    Loop
    RS.Close
    Return matches
End Sub

 

[William Lancee]

FYI

I am a Scrabble fan, so I happened to have a dataset of 370,000 words to test things on.
I tried @emexes SQL approach using word signatures like aabddennno FOR abandonned
The SELECT statement is as shown in post #7.

It took about 45min to build the SQL database (using the slow INSERT - not the Bulk Insert since the signature needed to be computed).
But after that, the Select query was correct and extremely fast: 2msecs for all words of any length with the letters in the hint "bandodaen"
There were 211 valid words.

The database is about 10MegaBytes.

I checked how that compared to a non-SQL native B4X code approach. The same task took 16msecs. Not bad, but 8 times slower than the SQL query.
Both methods gave the same results: 211 valid words. Those with 6 letters or more are:

abandoned
banande
adonean
abandon
abaddon
donned
bonded
banned
bandon
banded
anonad
andean
adeona
abonne
aboded

Conclusion: @emexes post is a very good solution.

The experiment database is too bulky to upload. So I have posted the code below to show what I did.

Sub Class_Globals
    Private Root As B4XView  'ignore
    Private xui As XUI
   
    Private words As List
    Private markTime As Long
   
    Private db As SQL
End Sub

Public Sub Initialize
    markTime = DateTime.now
    words = File.ReadList(File.DirAssets, "words_alpha.txt")    'This is my dictionary of 370,103 words - just the words
    Log($"Reading ${words.Size} words from file  ==>  Time = ${DateTime.Now - markTime} msecs"$)
    'Reading 370103 words from file  ==>  Time = 39 msecs

    Dim offset As Int = Asc("a")
    Dim n(26) As Int
    Dim w As String = "abandonned"  
    For j = 0 To w.length - 1
        Dim q As Int = Asc(w.charAt(j)) - offset
        n(q) = n(q) + 1
    Next
    Dim sb As StringBuilder
    sb.initialize
    For k = 0 To 25
        Dim letter As String = Chr(offset + k)
        Dim cnt As Int = n(k)
        For m = 0 To cnt - 1
            sb.Append(letter)
        Next
    Next
    Dim signature As String = sb.toString
    Log(signature)

   
    'create SQL db table1
    db.InitializeSQLite(File.DirApp, "lex.db3", True)

    markTime = DateTime.now
    Dim hint As String = "bandodaen"
    Dim offset As Int = Asc("a")
    Dim n(26) As Int
    For j = 0 To hint.length - 1
        Dim q As Int = Asc(hint.charAt(j)) - offset
        n(q) = n(q) + 1
    Next

    Dim sb As StringBuilder
    sb.initialize
    sb.Append("Select * FROM table1").Append(CRLF).Append("WHERE col2 Not LIKE ")
    For i = 0 To 25
        Dim sw As StringBuilder
        sw.initialize
        For j = 0 To n(i)
            sw.Append(Chr(offset + i))
        Next
        sb.Append($"'%${sw.ToString}%'"$)
        If i < 25 Then sb.Append(CRLF).Append("And col2 Not LIKE ")
    Next
    Dim Cursor As ResultSet
    Cursor = db.ExecQuery(sb.toString)
    Log($"SQL: Search for all valid words with letters in the hint ${hint}  ==>  Time = ${DateTime.Now - markTime} msecs"$)
    'SQL: Search for all valid words with letters in the hint bandodaen  ==>  Time = 3 msecs

    Dim validWords As List
    validWords.Initialize
    Do While Cursor.NextRow
        Dim w As String = Cursor.GetString("col1")
        validWords.Add((1000 + w.Length) & w)
    Loop
    validWords.Sort(False)
    Log("______valid words 6 or more letters____________")
    For i = 0 To validWords.Size - 1
        Dim w As String = validWords.Get(i)
        If w.Length < 10 Then Exit
        Log(w.SubString(4))
    Next
    Log(TAB)
    Log(TAB)
    Log("Native B4X: ____________________________________")
End Sub


Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Dim offset As Int = Asc("a")
    markTime = DateTime.now
    For i = 0 To words.Size - 1
        Dim w As String = words.Get(i)
        Dim n(26) As Int
        For j = 0 To w.length - 1
            Dim q As Int = Asc(w.charAt(j)) - offset
            n(q) = n(q) + 1
        Next
        words.Set(i, Array(w, n))
    Next
   
    Log($"Indexing all ${words.Size} words  ==>  Time = ${DateTime.Now - markTime} msecs"$)
    'Indexing all 370103 words  ==>  Time = 43 msecs

    Dim hint As String = "bandodaen"
    Dim m(26) As Int
    For j = 0 To hint.length - 1
        Dim q As Int = Asc(hint.charAt(j)) - offset
        m(q) = m(q) + 1
    Next

    markTime = DateTime.now
    Dim validWords As List
    validWords.Initialize
    For i = 0 To words.Size - 1
        Dim ar() As Object = words.Get(i)
        Dim w As String = ar(0)
        Dim n() As Int = ar(1)
        Dim invalid As Boolean
        For j = 0 To 25
            If n(j) > m(j) Then
                invalid = True
                Exit
            End If
        Next
        If Not(invalid) Then validWords.Add((1000 + w.Length) & w)
    Next
    Log($"Search for all valid words with letters in the hint ${hint}  ==>  Time = ${DateTime.Now - markTime} msecs"$)
    'Search for all valid words with letters in the hint bandodaen  ==>  Time = 16 msecs

    Log(TAB & TAB &  $"(result has ${validWords.Size} words)"$)
    Log(TAB)
   
    validWords.Sort(False)
   
    Log("______valid words 6 or more letters____________")
    For i = 0 To validWords.Size - 1
        Dim w As String = validWords.Get(i)
        If w.Length < 10 Then Exit
        Log(w.SubString(4))
    Next
   
'Reading 370103 words from File  ==>  Time = 41 msecs
'Indexing all 370103 words  ==>  Time = 44 msecs
'Search For all valid words with letters in the hint bandodaen  ==>  Time = 16 msecs

'        (result has 211 words)
'
'______valid words 6 or more letters____________
'abandoned
'banande
'adonean
'abandon
'abaddon
'donned
'bonded
'banned
'bandon
'banded
'anonad
'andean
'adeona
'abonne
'aboded
End Sub

'Code for creating the data base (slow method takes about 45 minutes - Bulk insert is faster)
'    db.ExecNonQuery("DROP TABLE IF EXISTS table1")
'    db.ExecNonQuery("CREATE TABLE table1 (col1 TEXT, col2 TEXT)")
'  
'    Dim offset As Int = Asc("a")
'    For Each w As String In words
'        Dim n(26) As Int
'        For j = 0 To w.length - 1
'            Dim q As Int = Asc(w.charAt(j)) - offset
'            n(q) = n(q) + 1
'        Next
'        Dim sb As StringBuilder
'        sb.initialize
'        For k = 0 To 25
'            Dim letter As String = Chr(offset + k)
'            Dim cnt As Int = n(k)
'            For m = 0 To cnt - 1
'                sb.Append(letter)
'            Next
'        Next
'        Dim signature As String = sb.toString
'        Log(signature)
'        db.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?)", Array As Object(w, signature))
'    Next
'    Log($"Created the SQL db with ${words.Size} words ==>  Time = ${DateTime.Now - markTime} msecs"$)

 

[RichardN]

I started with a string variable that contained a-z, then removed every character that was in the clue. I then updated a boolean variable in the database for each letter that was absent. I then executed

SELECT Word FROM EnglishWords WHERE ExcludeWord = 0 And LENGTH (Word) < 10 ORDER BY LENGTH (Word) DESC LIMIT 100

This worked fine until you realise you lose out on using the repeated characters. I am going to have a go at the pure SQL method suggested by @emexes now.

 

[RichardN]

@emexes You were sooooo close with your SQL query in post #7! I have compared methods as suggested above and debugging using B4J I think I have found the slickest. You need to do it like this...

SELECT Word FROM English WHERE....

Words with 3 instances of 'x' use... AND Signature NOT LIKE '%xxxx%'
Words with 2 instances of 'x' use... AND Signature NOT LIKE '%xxx%'
Words with 1 instance of 'x' use... AND Signature NOT LIKE '%xx%'

All other letters NOT present use... AND Signature NOT LIKE '%x%'

ORDER BY LENGTH(Word) DESC LIMIT 100

Constructing the text query from the signatures is a bit of a PITA but execution is fast with the single SQL query. My B4J example attached....

'Non-UI application (console / server application)
#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True     
    #AdditionalJar: sqlite-jdbc-3.36.0.2
    
#End Region

Sub Process_Globals
    Dim SQL As SQL
End Sub

Sub AppStart (Args() As String)
    
    SQL.InitializeSQLite("C:\DatabaseCentral","EnglishWords.db3",False)
    Dim Rs As ResultSet
    
    Dim MasterClue As String = "abandoned"
    Dim AnagramSignature As String = AnagramCreateSignature(MasterClue)    'Signature  looks like 'a2b1d1n2o1'
    
    Dim LetterCodes As List
    LetterCodes.Initialize
    
    For Count = 0 To AnagramSignature.Length -2 Step 2                    'Carve up a list of letter Signature codes
        LetterCodes.Add(AnagramSignature.SubString2(Count,Count +2))
    Next
    
    Dim ModAlphabet As String = "abcdefghijklmnopqrstuvwxyz"            'Start with the whole alphabet
    Dim ExcludePhrase As List
    ExcludePhrase.Initialize
    
    For Count = 0 To LetterCodes.Size -1
        
        Dim Letter, Letter2 As String
        Dim Frequency As Int
        Dim Code As String = LetterCodes.Get(Count)
        
        Letter = Code.CharAt(0)
        Letter2 = Code.CharAt(1)
        Frequency = Letter2                                                'Casting String to Int!
            
        Dim ModLetter As String
        ModLetter = Replicate(Letter,Frequency +1)                   
        ExcludePhrase.Add("%" & ModLetter & "%")                        'Append duplicate letters, add an extra letter and make it a wildcard '%xx% or '%xxx%'           
        
        ModAlphabet = ModAlphabet.Replace(Letter,"")                    'Remove all instances of single clue letters in Alphabet
    
    Next
    
    
    For Count = 0 To ModAlphabet.Length -1                                 'Append the single letters to the wildcard list
        
        ExcludePhrase.Add("%" & ModAlphabet.CharAt(Count)& "%")   
        
    Next

    ExcludePhrase.Sort(True)                                            'Makes debugging a lot easier!
        
    For Count = 0 To ExcludePhrase.Size -1                                'Construct the SQL conditionals within the existing list
        
        Dim Phrase As String
        Phrase = $"AlphaSignature NOT LIKE '${ExcludePhrase.Get(Count)}' "$
        ExcludePhrase.Set(Count,Phrase)
        
    Next                                                           
    
    
    Dim Sb As StringBuilder
    Sb.Initialize
    Sb.Append("SELECT Word FROM English WHERE ")
    
    For Count = 0 To ExcludePhrase.Size -1                                'Construct the SQL Query
        
        If Count < ExcludePhrase.Size -1 Then
            Sb.Append(ExcludePhrase.Get(Count) & "AND ")
        Else
            Sb.Append(ExcludePhrase.Get(Count))                            'AND' Is not needed on the last pass
        End If
            
    Next
    
    Sb.Append(" ORDER BY LENGTH(Word) DESC LIMIT 100")
    
    Dim Query As String = Sb.ToString
    Rs = SQL.ExecQuery(Query)
    
    Do While Rs.NextRow
        Log(Rs.GetString("Word"))
    Loop
    
End Sub



Sub AnagramCreateSignature(Anagram As String) As String              'Returns a signature like 'a2b1d1n2o1'
    
    Dim CharList As List
    CharList.Initialize
    Dim AnagramSorted As String
    
    'Sort the Anagram alphabeticly
    For i = 0 To Anagram.Length -1
        CharList.Add(Anagram.CharAt(i))
    Next
    
    CharList.Sort(True)
    
    Dim Sb As StringBuilder
    Sb.Initialize
    
    For i = 0 To CharList.Size -1
        Sb.Append(CharList.Get(i))
    Next
    
    AnagramSorted = Sb.ToString
    
    '--------------------------------------------------------------------------------------------
    
    'Create the signature
                                        
    Dim LetterCount As Int
    Dim Letter, Signature As String
                                        
    For a = 97 To 122
            
        LetterCount = 0
        Letter = Chr(a)
                
        If AnagramSorted.Contains(Letter) Then
            
            For i = 0 To Anagram.Length -1
                If Anagram.CharAt(i) = Letter Then LetterCount = LetterCount +1
            Next
            
        End If
        
        If LetterCount <> 0 Then Signature = Signature & Letter & LetterCount
                                            
    Next
        
    Return Signature
                                                                        
End Sub


Sub Replicate(subj As String, times As Int) As String        'Returns a character replicated in length X times
    
    Dim retval As String
    
    If times = subj.Length Then Return subj
    
    For count = 1 To times
        retval = retval & subj
    Next
    
    Return retval
    
End Sub

 

[emexes]

You were sooooo close

I think you and I are using the same "algorythm" but I explained by example and you've it explained better.

I feel like your constructing of the query is inside-out. But maybe there's something I'm not seeing. Let me get the first caffeine of the day into me, and I'll give it a go.

(for fun: if yours works, it works, and usually I'm like if it ain't broke, don't fix it... but it's an interesting puzzle anyway)

 

[emexes]

Running this outside-in code:

Sub AppStart (Args() As String)

    Log("*** Single line query ***" & CRLF & MakeQuery("abandoned", False))
    Log("*** Multiline query ***"   & CRLF & MakeQuery("abandoned", True ))

End Sub

Sub MakeQuery(MasterClue As String, MultiLineFlag As Boolean) As String
  
    If MasterClue <> MasterClue.ToLowerCase Then
        Log("MasterClue """ & MasterClue & """ is not lowercase")
    End If

    Dim ModAlphabet As String = "abcdefghijklmnopqrstuvwxyz"            'Start with the whole alphabet

    Dim Sb As StringBuilder
    Sb.Initialize
  
    Sb.Append("SELECT Word FROM English WHERE")
  
    Dim Glue As String = " "
    If MultiLineFlag Then Glue = CRLF & "    "
  
    For Count = 0 To ModAlphabet.Length - 1
        Dim Letter As Char = ModAlphabet.CharAt(Count)
  
        Sb.Append(Glue)
        Glue = " AND "
        If MultiLineFlag Then Glue = CRLF & "AND "
      
        Sb.Append("AlphaSignature NOT LIKE '%")
        Sb.Append(Letter)
      
        For I = 0 To MasterClue.Length - 1
            If MasterClue.CharAt(I) = Letter Then
                Sb.Append(Letter)
            End If
        Next 'I
      
        Sb.Append("%'")
    Next 'Count
  
    Glue = " "
    If MultiLineFlag Then Glue = CRLF
    Sb.Append(Glue)
  
    Sb.Append("ORDER BY LENGTH(Word) DESC LIMIT 100")
  
    Return Sb.ToString
  
End Sub

produces this:

Waiting for debugger to connect...
Program started.
*** Single line query ***
SELECT Word FROM English WHERE AlphaSignature NOT LIKE '%aaa%' AND AlphaSignature NOT LIKE '%bb%' AND AlphaSignature NOT LIKE '%c%' AND AlphaSignature NOT LIKE '%ddd%' AND AlphaSignature NOT LIKE '%ee%' AND AlphaSignature NOT LIKE '%f%' AND AlphaSignature NOT LIKE '%g%' AND AlphaSignature NOT LIKE '%h%' AND AlphaSignature NOT LIKE '%i%' AND AlphaSignature NOT LIKE '%j%' AND AlphaSignature NOT LIKE '%k%' AND AlphaSignature NOT LIKE '%l%' AND AlphaSignature NOT LIKE '%m%' AND AlphaSignature NOT LIKE '%nnn%' AND AlphaSignature NOT LIKE '%oo%' AND AlphaSignature NOT LIKE '%p%' AND AlphaSignature NOT LIKE '%q%' AND AlphaSignature NOT LIKE '%r%' AND AlphaSignature NOT LIKE '%s%' AND AlphaSignature NOT LIKE '%t%' AND AlphaSignature NOT LIKE '%u%' AND AlphaSignature NOT LIKE '%v%' AND AlphaSignature NOT LIKE '%w%' AND AlphaSignature NOT LIKE '%x%' AND AlphaSignature NOT LIKE '%y%' AND AlphaSignature NOT LIKE '%z%'  ORDER BY LENGTH(Word) DESC LIMIT 100
*** Multiline query ***
SELECT Word FROM English WHERE 
    AlphaSignature NOT LIKE '%aaa%' 
AND AlphaSignature NOT LIKE '%bb%' 
AND AlphaSignature NOT LIKE '%c%' 
AND AlphaSignature NOT LIKE '%ddd%' 
AND AlphaSignature NOT LIKE '%ee%' 
AND AlphaSignature NOT LIKE '%f%' 
AND AlphaSignature NOT LIKE '%g%' 
AND AlphaSignature NOT LIKE '%h%' 
AND AlphaSignature NOT LIKE '%i%' 
AND AlphaSignature NOT LIKE '%j%' 
AND AlphaSignature NOT LIKE '%k%' 
AND AlphaSignature NOT LIKE '%l%' 
AND AlphaSignature NOT LIKE '%m%' 
AND AlphaSignature NOT LIKE '%nnn%' 
AND AlphaSignature NOT LIKE '%oo%' 
AND AlphaSignature NOT LIKE '%p%' 
AND AlphaSignature NOT LIKE '%q%' 
AND AlphaSignature NOT LIKE '%r%' 
AND AlphaSignature NOT LIKE '%s%' 
AND AlphaSignature NOT LIKE '%t%' 
AND AlphaSignature NOT LIKE '%u%' 
AND AlphaSignature NOT LIKE '%v%' 
AND AlphaSignature NOT LIKE '%w%' 
AND AlphaSignature NOT LIKE '%x%' 
AND AlphaSignature NOT LIKE '%y%' 
AND AlphaSignature NOT LIKE '%z%' 
ORDER BY LENGTH(Word) DESC LIMIT 100
Program terminated (StartMessageLoop was not called).

 

[emexes]

think I have found the slickest.

Usually when looping through the alphabet I use:

For L = 1 to 26
    Dim Letter As Char = Chr(96 + L)    'a..z

but I did like your explicit technique:

Dim ModAlphabet As String = "abcdefghijklmnopqrstuvwxyz"            'Start with the whole alphabet
For Count = 0 To ModAlphabet.Length - 1
    Dim Letter As Char = ModAlphabet.CharAt(Count)

that could also work for non-ASCII alphabets, and digits, and even hyphens etc

 

[RichardN]

@emexes Yes I see you approached the problem from a different angle and ended up with less code in doing so, well done! Same destination... different road.

My English database does not contain any spaces or hyphens as they do not usually appear in word games. OTOH I have written similar stuff in French where you have a myriad of accented characters. Fortunately, you can either ignore the accents with SQL or simply un-accent the list of words in the database.