Solved

MySQL regexp / regular expressions help - scrabble solver

Posted on 2009-04-13
7
989 Views
Last Modified: 2013-12-12
I'm trying to build a scrabble solver - I can make it work, but not the way I want it to - basically I get all words less than or equal to the length of the input string, then walk through the restultset in my php script checking against the letter array built from the input string.

I know this is a bad - and slow - way to do it, and I'm sure there is a way to do it just from the query, but I'm struggling with this.

The example below assumes the input string is ABCDE, so finds all words which are 5 characters or less and only use the letters ABCDE - but the two problems with this are it allows letters to be repeated (which needs to be dependent on the input string), and it can't handle wildcards.

SELECT WORD FROM WORDLIST WHERE CHARACTER_LENGTH(WORD) <= 5 AND WORD NOT REGEXP '[^ABCDE]'

It would be quick enough to get the resultset and then filter out any words that use letters more than once (except of course if the input string contained eg. more than one 'A' and this is allowed), but this doesn't solve the wildcard problem.

I've looked at the mysql reference and I'm sure there must be a way to do this, but I've tried what seems like every combination and I'm not getting anywhere.

Does anyone have any suggestions? Is this actually possible through mysql alone?
0
Comment
Question by:rockers07
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

by:akshah123
ID: 24130599
You should try following ...

SELECT WORD 
FROM WORDLIST 
WHERE WORD REGEXP '^[ABCDE]{5}$'

Open in new window

0
 

Author Comment

by:rockers07
ID: 24130627
Thanks, but that only selects 5 letter words, and doesn't solve the duplicate letters or wildcard problems...
0
 
LVL 17

Expert Comment

by:akshah123
ID: 24130798
You will not be able to solve the duplication problem within the query.  That's just not possible unless you want to provide all possible combinations.  

IF you need to solve this problem multiple times, it might make more sense to have this data in memory instead.  

In any case, your only option is to take care of that in php.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:rockers07
ID: 24131201
Duplication problem solved:

SELECT WORD FROM WORDLIST
WHERE CHARACTER_LENGTH(WORD) <= 5
AND WORD NOT REGEXP '[^ABCDE]'
AND LENGTH(WORD) - LENGTH(REPLACE(WORD, 'A', '')) <= 1
AND LENGTH(WORD) - LENGTH(REPLACE(WORD, 'B', '')) <= 1
AND LENGTH(WORD) - LENGTH(REPLACE(WORD, 'C', '')) <= 1
AND LENGTH(WORD) - LENGTH(REPLACE(WORD, 'D', '')) <= 1
AND LENGTH(WORD) - LENGTH(REPLACE(WORD, 'E', '')) <= 1

And change to <= 2 if the letter can appear twice etc.

Not sure that this is the most efficient method though - easy enough to process the duplications in PHP using substr_count(), and this is feasible if the result has already been filtered to the right length and characters - but it still feels like this should be possible through regexp.

I'm also still left with the wildcard problem.....
0
 
LVL 41

Expert Comment

by:HonorGod
ID: 24131846
Use a GADDAG data structure...

http://www.cs.ubc.ca/local/reading/proceedings/spe91-95/spe/vol24/issue2/spe880.pdf

Which is a way of storing the entire (US English) Scrabble dictionary in such a way as to:

- consume lots less space
- make is very quick to search
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 24372348
Question PAQ'd, 500 points refunded, and stored in the solution database.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question