Search code examples
mysqlsqlstringwhere-clausesql-like

How to get vowels in Like operator of SQL?

I was solving a problem on SQL from Hackerrank. I have to make a query such that it gives me all city names starting with a, e, i, o or u. I'm using Like operator but still wrong answer.

Here's the problem Link

Here's my solution-

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[AEIOU]%'

Can anybody explain?

Solution

  • LIKE does not support that parttern. You need a regular expression match for this:

    SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[AEIOU]'

    In the regex, ^ represents the beginning of the string, and the square brackets define a custom character class (meaning that the first character must belong to that list).

    On the other hand, if you were to use LIKE, you would need multiple conditions, which would make the code lengthier (and probably less efficient):

    WHERE 
    CITY LIKE 'A%' 
    OR CITY LIKE 'E%' 
    OR CITY LIKE 'I%'
    OR CITY LIKE 'O%'
    OR CITY LIKE 'U%'