Query the list of CITY names from STATION that do not start with vowels and do not end with vowels

Question

This is a challenge question from HackerRank.

My first query is working fine:

select distinct city
from station
where city not regexp '^[aeiou]'
and   city not regexp '[aeiou]$'

But my second query is giving wrong answer

select distinct city
from station
where city not regexp '^[aeiouAEIOU].*[aeiouAEIOU]$'

It is compiling but giving wrong results

Answer 1

Select distinct city from station where not left(city,1) in ('a','e','i','o','u');

Answer 2

Tested and it works

    SELECT DISTINCT CITY
    FROM STATION
    WHERE CITY NOT REGEXP '^[aeiou]' AND CITY NOT REGEXP'[aeiou]$'

Answer 3

If the city has a case-folding collation, then you don't need the extra letters:

SELECT city
    FROM station
    WHERE city REGEXP '^[^aeiou].*[^aeiou]$';

And what about accents on letters? (I'm thinking of the town of Å, Norway.)

Explanations:

^ and $   -- anchors for start and end
[...]     -- character set
[^...]    -- inverse of character set
[^aeiou]  -- not a vowel
.*        -- zero or more characters

DISTINCT is unnecessary if you know no duplicate city names are in the table.

Answer 4

select DISTINCT CITY from STATION where CITY NOT LIKE 'a%' AND CITY NOT LIKE 'e%' AND CITY NOT LIKE 'i%' AND CITY NOT LIKE 'o%' AND CITY NOT LIKE 'u%';

Answer 5

select distinct city as cty from station where city not in ( select city from Station where Right(city,1) in ('a','e','i','o','u') and Left(city,1) in ('a','e','i','o','u') )

Answer 6

In Mysql,

where not CITY Regexp '^[aeiou].*$' or not CITY  Regexp '^.*[aeiou]$'

Answer 7

Most of us are reading the question wrong. They want either of the one condition to be fulfilled, not the both. The following solution works well in MS SQL Server :

select distinct city from station where (left(city,1) not in ('a','e','i','o','u') or right(city,1) not in ('a','e','i','o','u') );

Answer 8

I got the correct out put for the below code for MySql server

select distinct(city) from station 
where substr(city,1,1) not in ('a','e','i','o','u') or substr(city,-1,1) not in ('a','e','i','o','u')

Answer 9

SELECT DISTINCT CITY FROM STATION WHERE NOT LOWER(LEFT(CITY, 1)) IN ('a', 'e', 'i', 'o', 'u') AND NOT LOWER(RIGHT(CITY, 1)) IN ('a', 'e', 'i', 'o', 'u');
--This works for db2 databases

Answer 10

This is basic solution for this problem, I hope it will surely work.

Select Distinct City From Station 

Where City not LIKE 'a%a' and City not LIKE 'a%e' and City not LIKE 'a%i' and City not LIKE 'a%o' and City not LIKE 'a%u' and City not LIKE 'e%a' and City not LIKE 'e%e' and City not LIKE 'e%i' and City not LIKE 'e%o' and City not LIKE 'e%u' and City not LIKE 'i%a' and City not LIKE 'i%e'  and City not LIKE 'i%i' and City not LIKE 'i%o' and City not LIKE 'i%u' and City not LIKE 'o%a' and City not LIKE 'o%e' and City not LIKE 'o%i' and City not LIKE 'o%o' and City not LIKE 'o%u' and City not LIKE 'u%a' and City not LIKE 'u%e' and City not LIKE 'u%i' and City not LIKE 'u%o' and City not LIKE 'u%u';

Answer 11

SELECT DISTINCT city
FROM   station
WHERE  city NOT REGEXP '^[aeiouAEIOU].*|[aeiouAEIOU]$'

Explanation: I make sure the city does not start with ('^') vowels and also does not end with ('$') vowels.

Answer 12

Simplest code, you can try:

select distinct city
from station
WHERE lower(city) Not Rlike '^[aeiou].*[aeiou]$';

OR

SELECT DISTINCT city FROM station 
WHERE lower(city) not RLIKE '^[aeiou]'
and lower(city) not rlike '[aeiou]$';

Answer 13

This can also work:

SELECT DISTINCT city 
FROM station 
WHERE city NOT RLIKE  '[aeiou]$';

Answer 14

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

THIS IS FOR MS SQL USERS

SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[^aeiou]%' OR CITY LIKE '%[^aeiou]';

Answer 15

Try this it worked for me

select distinct city
  from station
 where city RLIKE'^[^aeiouAEIOU].*$';

Answer 16

Here is a query using a Where column Not Rlike clause.

This does a pattern match of a string expression against a pattern.

Select Distinct city 
From station 
Where city Not Rlike '^[aeiouAEIOU].*[aeiouAEIOU]$';

Additionally, the Distinct is used to remove all results that are the same.

Answer 17

SELECT DISTINCT city
FROM   station
WHERE  NOT city LIKE 'A%'
       AND NOT city LIKE 'E%'
       AND NOT city LIKE 'I%'
       AND NOT city LIKE 'O%'
       AND NOT city LIKE 'U%'; 

a lengthy way but you will get the answer

Answer 18

I consider both the answer right.

But some how first query is failed whereas second one is passed from hackerrank platform.

First query

SELECT DISTINCT city FROM station 
WHERE city not RLIKE '^[aeiou].*[aeiou]$';

Second query : working with no issue

SELECT DISTINCT city FROM station 
WHERE city not RLIKE '^[aeiou]'
and city not rlike '[aeiou]$';

Answer 19

Try this

select distinct city
from station
where city rlike '^[^aeiouAEIOU].*' and city rlike '.*[^aeiouAEIOU]$'

Answer 20

Here is the right answer:

select DISTINCT(CITY)
from STATION
Where CITY RLIKE '^[^aieouAEIOU].*[^AEIOUaeiou]$';

Answer 21

Use this MySQL Command:

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

Explanation: DISTINCT is used to remove duplicates from the result set of a SELECT statement. "^[^aeiou]" means city names that do not start with vowels. "[^aeiou]$" means city names that do not end with vowels. ".*" represents any string of any length.

Answer 22

You need to take care of 3 cases when using any DB
1. Word starts with vowel but ends with consonant
2. Word starts with consonant but ends with vowel
3. Word starts with consonant and ends with consonant

my solution implemented in mySQL

select distinct(CITY) from station where CITY RLIKE '(^[^aeiouAEIOU].*[aeiou]$|^[aeiouAEIOU].*[^aeiou]$|^[^aeiouAEIOU].*[^aeiou]$)';

Answer 23

This works in MS SQL server :

SELECT DISTINCT city FROM station WHERE city NOT LIKE '[AEIOU]%' AND city NOT LIKE '%[aeiou]';

Answer 24

This worked for me using the RLIKE operator... Hope this helps:

select distinct CITY from STATION where CITY NOT RLIKE '^[aeiouAEIOU]' AND CITY NOT RLIKE '[AEIOUaeiou]$' GROUP BY CITY;

Answer 25

https://dev.mysql.com/doc/refman/8.0/en/regexp.html

'NOT REGEXP' is Negation of REGEXP,

'REGEXP' -> Checks the 'REGEXP' if satisfied then return 1 else 0

    select 'Chelsea' REGEXP '^[aeiou].*[aeiou]$' ;

        0

'NOT REGEXP' -> checks the 'REGEXP' then return the opposite of the result.

    select 'Chelsea' not REGEXP '^[aeiou].*[aeiou]$' ;

        1

In this case, 'Chelsea' doesn't start with vowel hence REGEXP is 0 and not REGEXP is 1.

Hence you are counting the cities, even the name starts or ends with vowels.