How do I query for Strings containing a letter more than once?

Question

How do I run a query in MySQL to search for Strings containing a character on more than one occurrence?

SELECT * FROM animals WHERE name LIKE '%r%' will only return animals that contain an 'r'..

+---------+------------+
|    id   | name       |
+---------+------------+
|     1   | zebra      |
|     14  | raccoon    |
|     25  | parrot     | 
|     49  | rhinoceros |
+---------+------------+

SELECT * FROM animals WHERE name LIKE '%rr%' will only return animals that contain an occurance of 'rr'..

+---------+------------+
|    id   | name       |
+---------+------------+
|     25  | parrot     | 
+---------+------------+

I would like to find any animal names that contain an 'r'.. lets say twice anywhere in the name.

+---------+------------+
|    id   | name       |
+---------+------------+
|     25  | parrot     | 
|     49  | rhinoceros |
+---------+------------+

Anyone?

Answer 1

Have you tried this?

select *
from animals
where name like '%r%r%'

An alternative solution is to use length and replace:

select *
from animals
where length(name) - length(replace(name, 'r', '')) >= 2;

This could be advantageous if you were looking for occurrences of a set of letters, for instance 'r' and 's':

select *
from animals
where length(name) - length(replace(replace(name, 'r', ''), 's', '')) >= 2;

EDIT:

If you want exactly two "r"s, you can just use equality in the where clause:

select *
from animals
where length(name) - length(replace(name, 'r', '')) = 2;

Answer 2

You can go about it indirectly, by checking how much a string's length changes when you REMOVE those characters:

SELECT id, name
FROM yourtable
WHERE (length(name) - length(replace(name, 'r', ''))) >= 2

e.g. parrot has 6 chars, and with the r removed, is only 4, so 6-4=2 and would match the where.