Question 241078 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL Basic Select

Hello since I am new to mysql I have one question regarding selection. I am studying on https://www.w3schools.com/ and practicing on https://www.hackerrank.com/.

On hackerrank.com I have a question:

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

So someone more qualified explain why does code:

SELECT DISTINCT CITY 
FROM STATION
WHERE CITY LIKE '[aeiouAEIOU]%';

works on www.w3schools.com

and does not work on hackerrank.com.

But this one:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiouAEIOU]'
ORDER BY CITY;

do work.

Tags:

2

Answers


  1. 0

    You have a typo: It should be CITY, not CIRY.

    SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[aeiouAEIOU]%';

    No idea why it works on http://www.w3schools.com though.

    If you only mistyped here on StackOverflow, then the problem is probably that pattern matching and regular expressions are handled differently by different database systems, so the interpretation of certain pattern matching syntax can differ.

    Login or Signup to reply.
  2. 0

    Your question is worth answering, because your first query:

    SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[aeiouAEIOU]%';

    in fact would be valid and would run on other databases, such as SQL Server. But MySQL does not support the [aeiouAEIOU] enhanced LIKE syntax. So you need to use REGEXP instead. If you wanted to use LIKE only with MySQL, then you could use:

    SELECT DISTINCT CITY
FROM STATION
WHERE UPPER(CITY) LIKE 'A%' OR
      UPPER(CITY) LIKE 'E%' OR
      UPPER(CITY) LIKE 'I%' OR
      UPPER(CITY) LIKE 'O%' OR
      UPPER(CITY) LIKE 'U%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search