0

I'm new to regular expressions and after searching and experimenting I cannot find a solution. I want to search a database with words. The search pattern contains possibly letters that should be present in the returned words. Example pattern: pale should return word containing 'p' and / or 'a' and / or 'l' and / or 'e':

Possible words (if in database): pal, lea, epa, pale, elap etc ...

My question is how to build a regular expression that can perform the above.

Regards.

Improve this question
1

1 Answer 1

Reset to default
1 
SELECT *
FROM MyTable
WHERE word RLIKE '^[pale]+$'

A regular expression to honor the letter counts would look like:

^(p?(a?(l?e?|e?l?)|l?(a?|e?)|e?(a?lf?|l?a?))|a?(p?(l?e?|e?l?)|l?(p?e?|e?p?)|e?(p?l?|l?p?))|l?(a?(p?e?|e?p?)|p?(a?e?|e?a?)|e?(a?p?|p?a?))|e?(p?(a?l?|l?a?)|a?(p?l?|l?p?)|l?(a?p?|p?a?)))$

As you can see, there's an exponential growth in the size of the regexp based on the length of the input.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks. Tried that and it almost works but words containing multiple 'p' for instance are also returned, 'appell'. Is there a way to restrict the query to honor the letter count in the pattern?
No, I don't think regular expressions can do that without combinatorial explosion in the size of the regexp.
Ok, thanks for clarifying that. I ended up using your expression and on a second run removed all words where the lettercount for each letter was exceeded. Working great now. Thanks again.
A beautiful way to calculate the count of a certain character in a string: stackoverflow.com/questions/4736058/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.