CODEWITHSUNDEEP
mysqlsql
problem
problem

Reputation: 1

Can anyone explain what is wrong wtih the below query

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY 
FROM STATION 
WHERE (SUBSTR(CITY,1,1) 
AND SUBSTR(CITY,-1,1)) NOT IN ("a","e","i","o","u");

Can someone explain what's wrong with this MySQL query?

Upvotes: 0

Views: 72

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

Both the first and last letters require assertions:

SELECT DISTINCT CITY 
FROM STATION 
WHERE LEFT(CITY, 1)  NOT IN ('a', 'e', 'i', 'o', 'u') AND
      RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u');

We can also handle this with regular expressions:

SELECT DISTINCT CITY 
FROM STATION
WHERE CITY REGEXP '^[^aeiou].*[^aeiou]$';

Upvotes: 3

Related Questions