Reputation: 417

Get city name either do not start with vowels or do not end with vowels

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.

Input Format

The STATION table is described as follows:

enter image description here

I write the below query, but it's not working for me. Any suggestion?

select distinct city
from station
where city regexp '^[^aeiou].*[^aeiou]$';

Upvotes: 29

Answers (30)

Anand G

Reputation: 3210

Assuming you are using MySQL, Here is what you are looking for

SELECT DISTINCT city FROM station WHERE city RLIKE '^[^aeiouAEIOU].*|.*[^AEIOUaeiou]$';

Footnote : RLIKE and DISTINCT

What worked for me for MySQL:

SELECT DISTINCT city FROM station
WHERE LOWER(city) NOT REGEXP '^[aeiou]'
AND LOWER(city) NOT REGEXP '[aeiou]$';

Note: I'm quite unsure why, but RLIKE worked for the 2 challenges prior to this (start only, end only). Now, REGEXP is the only one working. Can someone explain to me why it is so?

Upvotes: 17

noobmaster007

Reputation: 163

If you are using MS SQL:

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

I noticed that most answers here uses "AND", but the question is to select cities that either starts with a vowel or ends with a vowel. So you should be using OR.

Upvotes: 0

Tamara Koliada

Reputation: 1194

Simple and clean answer:

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

Upvotes: 0

Raj Praveen Pradhan

Reputation: 21

SELECT DISTINCT CITY FROM STATION 
WHERE CITY REGEXP '^[^aeiou]' /*Checks City does not start with vowel*/
AND  CITY REGEXP '[^aeiou]$'; /*Checks City does not end with vowel*/

Upvotes: 0

Rameez Ahmad Dar

Reputation: 871

SELECT DISTINCT CITY FROM STATION WHERE lower(right(CITY,1)) not in('a','e','i','o','u');

For City not ending with vowel

Upvotes: 0

Akhand pratap singh

Reputation: 29

This one is for MySQL, just tried now, and its works.

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

Upvotes: 0

Shashank

Reputation: 324

for MySQL

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP "^[^aeiouAEIOU].*[^aeiouAEIOU]$"

Upvotes: 0

Jorge Ruiz

Reputation: 11

I use this and it works, its ORACLE SQL:

select distinct city from station where regexp_like(city, '^[^aeiou].*|.*[^aeiou]$', 'i');

Upvotes: 1

Mahmud Hosen

Reputation: 53

My SQL Query:

SELECT DISTINCT CITY FROM STATION 
WHERE(CITY NOT LIKE 'u%' AND 
CITY NOT LIKE 'a%' AND 
CITY NOT LIKE 'e%' AND 
CITY NOT LIKE 'i%' AND 
CITY NOT LIKE 'o%') OR
( CITY NOT LIKE '%u' AND 
CITY NOT LIKE '%a' AND 
CITY NOT LIKE '%e' AND 
CITY NOT LIKE '%i' AND 
CITY NOT LIKE '%o');

Upvotes: 0

Ruchir Dixit

Reputation: 125

Simple MySql query can be like :

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

Upvotes: 0

Shubham Parmar

Reputation: 165

For Oracle : select DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^aeiou]|[^aeiou]$','i');

Upvotes: 0

karthik8fd

Reputation: 51

This is a required solution for MS SQL SERVER,

select distinct city from station where city not like '[aeiouAEIOU]%' and city not like '%[aeiouAEIOU]'

Upvotes: 3

Neeraj Kumar

Reputation: 89

For MySQL Select DISTINCT CITY from STATION where CITY REGEXP '^[^aeiou]|[^aeiou]$';

Upvotes: 2

Anil Sapkota

Reputation: 33

You could try this for MySQL:

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

This query will bring you the desired output for the asked question.

Upvotes: 4

Vivek Gajbhiye

Reputation: 86

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT like '%a' and CITY NOT like '%i' and CITY NOT like '%e' and CITY NOT like '%o' and CITY NOT like '%u';

Upvotes: 0

Rohan Aher

Reputation: 39

SELECT DISTINCT CITY 
FROM STATION 
WHERE 
  SUBSTRING(CITY,1,1) NOT IN ('A','E','I','O','U') 
  OR 
  SUBSTRING(CITY,1,1) NOT IN ('a','e','i','o','u')

This code works for MS SQL SERVER.

Upvotes: 1

user6117820

Reputation:

Correction to the above statement using MS SQL SERVER is shown below:

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE '[AEIOU]%' OR CITY NOT LIKE '[aeiou]**%**';

Upvotes: 0

Sumeet Kumar

Reputation: 143

MYSQL:

Select distinct city from station where city not regexp '^[aeiou].*[aeiou]$'

EXPLANATION : Using regular expression and NOT condition. So basically it is searches for the city which start with vowels '^[aeiou]' concatenated by 'n' values in between by '.' operator and second condition of ending with vowels '[aeiou]$'. And at the end since NOT condition is applied ,so only the ones whic do not meet the criteria are filtered out and displayed.

Upvotes: 1

dnyaneshwar mogal

Reputation: 39

for oracle try this:

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^.*[^aeiouAEIOU]$') 
UNION 
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^aeiouAEIOU].*$');

Upvotes: 0

Nandhini

Reputation: 31

A solution to your question should be the following query:

select DISTINCT CITY from STATION where CITY NOT LIKE '[a,e,i,o,u]%' OR
CITY NOT LIKE '%[a,e,i,o,u]'

Upvotes: 2

ebuzz168

Reputation: 1194

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

Upvotes: 7

user12660192

Reputation: 1

mysql

Answer for "dont start with vowels either dont end with vowels" is SELECT DISTINCT CITY FROM STATION WHERE NOT CITY RLIKE '^[AEIOUaeiou]' or NOT CITY RLIKE '.*[AEIOUaeiou]$' ;

Answer for "dont start with vowels and dont end with vowels" is SELECT DISTINCT CITY FROM STATION WHERE NOT CITY RLIKE '^[AEIOUaeiou]' And NOT CITY RLIKE '.*[AEIOUaeiou]$' ;

Upvotes: 0

Abhishek Shukla

Reputation: 72

As the requirement is mentioned that the start and end should not be Vowel, here we can use a Pattern using RLIKE:

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'));

Upvotes: 0

Mina Wissa

Reputation: 10971

Try this:

SELECT DISTINCT CITY FROM STATION WHERE CITY NOT RLIKE '^[aeiouAEIOU]' 
AND
CITY NOT RLIKE '[aeiouAEIOU]$'

Upvotes: 3

vivek arya

Reputation: 1

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');

Upvotes: -1

Amanda Lan

Reputation: 1

MySQL RLIKE operator performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument.

SELECT DISTINCT city FROM station 
WHERE city NOT RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$';

Ref: https://www.w3resource.com/mysql/string-functions/mysql-rlike-function.php https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp

Upvotes: 0

Janis Janovskis

Reputation: 17

How about this one:

SELECT CITY from STATION WHERE LOWER(CITY) NOT REGEXP '^[aeiou].*[aeiou]$' GROUP BY CITY

Upvotes: 0

LohitPant

Reputation: 1

Assuming that you saw this question on hacker rank and given your screenshot of the table STATION schema also tells the same thing. Here is my answer and it worked for me in MYSQL.

select DISTINCT(city) from STATION where CITY REGEXP '^[^aeiou]' or CITY REGEXP '.*[^aeiou]$';

Upvotes: 0

Navin Kumar

Reputation: 1

In MySQL regular expressions are the same as in Python.

You can use regular expressions in this case as well.

Select the cities which start and end with vowels.
Next, put not in front, which will eliminate those cities.

The query will be:

select distinct(city) from station where not (city rlike '^[aeiouAEIOU]' and city rlike '[aeiouAEIOU]$');

Upvotes: -1

Nikhil Kurkure

Reputation: 67

SELECT distinct city FROM station 
WHERE City not LIKE '[aeiou]%' and CITY NOT LIKE '%[aeiou]'

Upvotes: 0

Get city name either do not start with vowels or do not end with vowels

Answers (30)

Related Questions