1

With the following query

SELECT * FROM words WHERE word REGEXP '(ord)'
  • This will return words like ford, lord, word

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

  • For example adore, door, random

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?

MrUpsidown
  • 21,592
  • 15
  • 77
  • 131
  • 1
    `[ord]{3}`..... but this would find `rod`, but not `orsd`, because `s` is not part of the class and the `{3}` requires the three chars to be consecutive. – Marc B Apr 21 '15 at 15:40
  • 1
    What problem are you trying to solve with this? Seems like a very odd requirement that might have a much better approach available. – ceejayoz Apr 21 '15 at 15:40
  • I am fine with any approach... My goal is to retrieve words from a table that *contain* some letters, in any order. All letters must appear. – MrUpsidown Apr 21 '15 at 15:42

3 Answers3

2

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
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • I am a bit lost with your query. My table is `words` and the column is `word`. What would be the complete syntax? – MrUpsidown Apr 21 '15 at 15:51
  • No, it's not the complex syntax, just showing the critical part - checking if each particular character is present and only allowing those records where all three are there through. ricKJames's version below is a bit easier to understand, but my version lets you potentially set restrictions on how many of each character you want (e.g. 3 o's, 2 d's, and 1 r). – Marc B Apr 21 '15 at 17:38
  • OK, I think I got the syntax. How do you set restrictions? I have tried `IF(LOCATE('r', words) > 0, 2, 0)` but that doesn't seem to work. – MrUpsidown Apr 22 '15 at 07:26
  • you separate the individual if() statements into their own aliases, then `having r_count = X, o_count=Z, etc..` – Marc B Apr 22 '15 at 14:04
  • Would you mind updating your answer with an example? – MrUpsidown Apr 22 '15 at 14:26
2

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.

Greg Kelesidis
  • 1,069
  • 14
  • 20
1

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    |
+--------------------+-------+
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • +1 for the complete query. What if I want to input the same letter twice? For example, words containing `o, r, r, d`. – MrUpsidown Apr 22 '15 at 07:16
  • Thanks for that. Is `locate` supposed to be faster than `REGEXP`? – MrUpsidown Apr 22 '15 at 16:12
  • I don't have any benchmarks, but I would expect `locate` to be a lot faster than `regexp`. However, I could not think of a way to do 2`r` easily and quickly without `regexp`. The real performance hit is having to read every row (no index is helpful) and scan the column multiple times. – Rick James Apr 22 '15 at 16:23
  • Also, `LIKE` might be faster: `city LIKE '%o%'`. – Rick James Mar 21 '21 at 18:34