MySQL REGEXP functionality... any way to make this easier?

[xepherys]

Right now, a sample query I might have would be...

SELECT * FROM ospd WHERE word REGEXP '^{([a]?[p]?[e]?)$|[p]?[a]?[e]?$|[a}
]?[e]?[p]?$|^{[p]?[e]?[a]?$|[e]?[p]?[a]?$|[e]?[a]?[p]?$';}

where basically I need to query every combination of a, p and e. This is a pain, but for only three letter is not so bad. As you can see, this could become unruly with larger numbers of letters. Each instance of a letter can only occur once. However, a letter may be duplicated and used once for each instance. Hence the statement steps through... it could also be something like...

SELECT * FROM ospd WHERE word REGEXP '^{[a]?[a]?[e]?$|[a]?[e]?[a]?$||[e]?[a]?[a]?$';}

more easily written because one letter is used twice and can occure once for each instance. Please let me know if you have any ideas.

Jesse
[e-mail]

[tomhath]

If letters are allowed to be used more than once you can just used something like this (didn't test it):

'^[a-z]{3,3}$'

[xepherys]

they can only be used more than once per instance they are given... and a-z doesn't work as that allows for ALL letters.

Basically, someone will post a group of letters from a webpage... that POST data is where I get the letters to PHP... it could be 'ABCDE' or it could be 'XYZZY'. In the latter case, Z and Y could both be used 0, 1 or 2 times... X could be used 0 or 1 time.

-Jesse

[tomhath]

How about sorting the letters in the posted string and keeping a new column of the data you are looking for with the unique letters sorted?

E.g.

DataCol SearchCol
XYZZY XYZ
XYZYZ XYZ
XXXZZ XZ
... etc

[xepherys]

again, this won't work, since the letters that are supplied more than once need to be useable more than once... here's an example... there's a wordlist in an SQL DB. The user goes to the website and enters "SAPLPE". Possible results might include:

APPLE APPLES PAL PALS PALE PALES SAP

However, if the user supplied only "SAPLE", APPLE and APPLES would not be valid selections, as 'P' only appears once. See what I mean?

-Jesse

[tomhath]

Then use all the letters in the word and make the regex slightly more complicated:

If they pick SAPLE you search for

/^{A{0,1}E{0,1}L{0,1}P{0,1}S{0,1}$/}

But if they pick SAPLPE you search for

/^{A{0,1}E{0,1}L{0,1}P{0,2}S{0,1}$/}

DataCol SearchCol
APPLE AELPP
APPLES AELPPS
PAL ALP
PALS ALPS
PALE AELP
PALES AELPS
SAP APS

[xepherys]

Almost... but still not quite. Anagrams don't nessecarily use the letters in the same order...

so if they put in A P P L E S, they'll get APPLE, APPLES, APE, APES, etc.

if they put in A P P L S E, they'll get APPLE, but never APPLES.