CODEWITHSUNDEEP
mysql
Subhayan Bhattacharya
Subhayan Bhattacharya

Reputation: 5715

Write a MySQL query which selects countries having all the vowels present in their name

I Have a table called world in which i am supposed to select the name of countries which do not have spaces in between their name and the name contains all the vowels in English alphabet. I tried this but it is not working:

select name from world where name not like '% %' and name like '%a%e%i%o%u%'

I can't give the table structure and contents here since i don't have access to the entire table's contents. Basically i want to know the regex that i have to use .

An example of the answer is : 'Mozambique'

Upvotes: 2

Views: 755

Answers (3)

Trung Duong
Trung Duong

Reputation: 3475

You could use REGEXP to archive result that you want. The below regular expression pattern means that the name must contains all the vowels in English alphabet.

SELECT name 
FROM world
WHERE name REGEXP '[aeiou].*[aeiou].*[aeiou].*[aeiou].*[aeiou]'

Upvotes: 2

campovski
campovski

Reputation: 3153

Use CONTAINS(column, string) like that:

SELECT name FROM world
WHERE
NOT CONTAINS(name, " ")
AND CONTAINS(name, "a")
AND CONTAINS(name, "e")
AND CONTAINS(name, "i")
AND CONTAINS(name, "o")
AND CONTAINS(name, "u");

Or with LIKE statement:

SELECT name FROM world
WHERE
name NOT LIKE "% %" AND
name LIKE "%a%" AND
name LIKE "%e%" AND
name LIKE "%i%" AND
name LIKE "%o%" AND
name LIKE "%u%";

Upvotes: 3

Mário Fernandes
Mário Fernandes

Reputation: 1862

One simple way, although maybe not the most effective would be:

select name from world where name not like '% %' and name like '%a%' and name like '%e%' and name like '%i%' and name like '%o%' and name like '%u%'

Not counting uppercase letters as well.

Upvotes: 2

Related Questions