Given the following database table:
WORDS
alphagram....varchar(15)
word.........varchar(15) PK
length.......int
Where:
I've found a way to find the anagrams of a given string of letters via SQL. For example, to find the anagrams of AEINNRTT this will work:
select alphagram, word, definition
from words
where length = 8
and alphagram like '%A%'
and alphagram like '%E%'
and alphagram like '%I%'
and alphagram like '%NN%'
and alphagram like '%R%'
and alphagram like '%TT%'
That will return 1 row (for INTRANET)
And if I wanted to include a known number of wildcards, for example, how many words are with INTRANET + a blank (wildcard) I just have to change the 'length' to the total number of letters + number of wild cards
e.g.
select alphagram, word, definition
from words
where length = 9
and alphagram like '%A%'
and alphagram like '%E%'
and alphagram like '%I%'
and alphagram like '%NN%'
and alphagram like '%R%'
and alphagram like '%TT%'
...will return 8 rows (ENTERTAIN, INSTANTER, INTEGRANT, INTRANETS, ITINERANT, NATTERING, RATTENING, and TRANSIENT)
My question is this: is there a more efficient way of doing this via SQL only?
This works pretty fast in SQLServer but pretty slow in SqlLite. I realise that the %xxx% searches are not fast.
You could create a kind of index column for each entry that has all the letters of the word in alphabetical order and then compare these. Each anagram will have the same index value.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With