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 this query:

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

Answer 2

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?

Answer 3

SELECT name
FROM world
WHERE LENGTH(name) - LENGTH(REPLACE(lower(name),'a', '')) >= 3

Answer 4

This is worked for me:

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