CODEWITHSUNDEEP
mysqlregex
MrUpsidown
MrUpsidown

Reputation: 22489

MySQL find words containing all letters at any place

With the following query

SELECT * FROM words WHERE word REGEXP '(ord)'

How can I include words where these letters appear but not in the same order?

Edit:

Got this working.

SELECT word, (
    IF(LOCATE('o', word) > 0, 1, 0) + 
    IF(LOCATE('r', word) > 0, 1, 0) +
    IF(LOCATE('z', word) > 0, 1, 0) +
    IF(LOCATE('a', word) > 0, 1, 0) +
    IF(LOCATE('d', word) > 0, 1, 0)) AS chars_present
from words
HAVING chars_present = 5

Now how would I query for words containing the letter r twice?

Upvotes: 1

Views: 2448

Answers (3)

Rick James
Rick James

Reputation: 142518

This may be the fastest:

SELECT *
    FROM words
    WHERE LOCATE('o', word)
      AND LOCATE('r', word)
      AND LOCATE('d', word);

mysql> SELECT city, state FROM us
      WHERE locate('o', city) AND locate('r', city) AND locate('d', city)
      LIMIT 11;
+---------------+-------+
| city          | state |
+---------------+-------+
| Irondale      | AL    |
| Oxford        | AL    |
| El Dorado     | AR    |
| Paragould     | AR    |
| Sherwood      | AR    |
| Goodyear      | AZ    |
| Safford       | AZ    |
| Alondra Park  | CA    |
| Anderson      | CA    |
| Arroyo Grande | CA    |
| Atascadero    | CA    |
+---------------+-------+
11 rows in set (0.00 sec)

If you need two r, the test for them would be word REGEXP 'r.*r':

mysql> SELECT city, state FROM us
    ->           WHERE locate('o', city) AND city REGEXP 'r.*r' AND locate('d', city)
    ->           LIMIT 5;
+--------------------+-------+
| city               | state |
+--------------------+-------+
| Alondra Park       | CA    |
| Arroyo Grande      | CA    |
| Corte Madera       | CA    |
| Desert Hot Springs | CA    |
| Garden Grove       | CA    |
+--------------------+-------+

Upvotes: 1

Greg Kelesidis
Greg Kelesidis

Reputation: 1054

Just repeat the rule for each letter.

SELECT * FROM words WHERE word REGEXP 'o' AND word REGEXP 'r' AND word REGEXP 'd'

The order in which rules appear, doesn't matter.

Upvotes: 2

Marc B
Marc B

Reputation: 360842

It'd be ugly doing a regex in mysql to require all three chars to be present in any location, and you might be better off with something like:

SELECT (
    IF(LOCATE('o', words) > 0, 1, 0) + 
    IF(LOCATE('r', words) > 0, 1, 0) +
    IF(LOCATE('d', words) > 0, 1, 0)) AS chars_present
...
HAVING chars_present = 3

Upvotes: 2

Related Questions