How to solve # 7 in SQLZOO select names section

Question

All:

I am learnig SQL now, but stuck at #7 of

http://sqlzoo.net/wiki/SELECT_names

Bahamas has three a - who else?

Find the countries that have three or more a in the name

Thanks

Answer 1

Try using the LIKE operator:

SELECT name FROM world
WHERE name LIKE '%a%a%a%'

If you want to do case-insensitive search for either a or A then you can use the LOWER() function:

SELECT name FROM world
WHERE LOWER(name) LIKE '%a%a%a%'

Edit:

We could also use REGEXP here:

SELECT name FROM world
WHERE name REGEXP '(.*[a]){3,}';

However, for this particular example, I would go with LIKE, because it probably would perform better, and less of an overhead, than using REGEXP.

Answer 2

Try this query:

SELECT name
FROM world
WHERE LEN(name) - LEN(REPLACE(name,'a', '')) > 2

Answer 3

I'm trying to solve this lecture with regular expressions but couldn't match this correctly.

regexp '(.*[a]){3,}'

Should match 3 or more 'a', which it does but:

"Sao Tomé and Príncipe" with 2 a's is matched by LIKE '%a%a%a%' which is the correct answer.

Im curious what's going wrong here?