LIKE with multiple character conditions using percentage

Question

I've got a query that returns the name of a country, where the name must include all of the vowels (a,e,i,o,u).

I'd like to transform this:

SELECT name FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'

into a 2 line query. I tried this, but it didn't work:

SELECT name FROM world
WHERE name LIKE '%a%e%i%o%u%'

I though %a%e%i%o%u% should've done it, but I guess I haven't fully grasped the concept yet.

`'%a%e%i%o%u%'` would only match names which contained all of the vowels **in that specific order**. If you need to match them in any order, you'll need the multiple `LIKE` conditions from your first query. — Richard Deeming, Feb 11 '22 at 13:53

score 1 · Answer 1 · edited Nov 09 '22 at 23:23

1

You can use the wildcard like this:

SELECT name FROM world
WHERE name LIKE '%[aeiou]%';

edited Nov 09 '22 at 23:23

buddemat

answered Nov 07 '22 at 00:55

Victor Forget

There is condition that all vowels must be included. The square bracket indicates it is performing OR operation on it which clearly not what is expected. Because then it will satisfy the condition even when there one of those characters in the word. So, the solution would be specifying multiple LIKE conditions with AND operations – Dhruv Shah Feb 27 '23 at 12:22

score 0 · Answer 2 · answered Feb 11 '22 at 14:54

0

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

answered Feb 11 '22 at 14:54

Ushakiran Dhoke

That's essentially the same as the first query in the question, with the addition of the "no spaces" filter which was never mentioned as a requirement. – Richard Deeming Feb 11 '22 at 14:58

2 Answers2