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

Question

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 works fine. Any suggestion?

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

Which DB is this ? Most DB do not support regex. Use functions that your DB supports. — Ari Singh, Mar 12 '18 at 06:00
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — a_horse_with_no_name, Mar 12 '18 at 08:56
@AriSingh: actually most DBMS **do** support regular expressions. But as there is no real SQL standard (at least for real POSIX regex) the syntax is very different for each DBMS product — a_horse_with_no_name, Mar 12 '18 at 08:57
"either/or" sounds like it is allowed to *both* start and end on a vowel? — Hans Keﬆing, Mar 12 '18 at 09:55

score 10 · Accepted Answer · edited Feb 07 '21 at 05:29

10

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

edited Feb 07 '21 at 05:29

Ambesh Shekhar

3
2

answered Mar 12 '18 at 06:07

Anand G

2,880
1
20
28

You are assuming that the OP is using `MySQL`. Answering when the question is halfheartedly tagged, only serves to confuse anyone else who comes along with a similar question. – Peter Abolins Mar 12 '18 at 09:07
Agree! Edited my answer, though I just modified the query in OPs answer. Though RLIKE is different for diff databases – Anand G Mar 12 '18 at 09:13
In MySQL, `RLIKE` and `REGEXP` are equivalent. – Barmar Jul 07 '19 at 07:33
@AnandG What will be in the MS SQL Server? – Taylor Aug 01 '19 at 12:21

Vignesh VS · Answer 2 · 2018-03-12T12:15:07.143

This SQL Query will helpful for you. If you are using MS SQL then follow this line of code given below:

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

If you are using MySQL then follow this line of code given below:

SELECT DISTINCT CITY 
FROM STATION 
WHERE (CITY NOT IN (SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%o' OR CITY LIKE '%u'))
OR 
(CITY NOT IN (SELECT CITY FROM STATION WHERE CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%'));

score 6 · Answer 3 · answered Oct 19 '18 at 14:14

6

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

You have to place caret character inside square brackets which means not any vowels. Here is explanation

answered Oct 19 '18 at 14:14

rob111

1,191
1
13
15

Works in MySQL. – Suresh Mangs Oct 02 '20 at 12:55

score 6 · Answer 4 · edited Feb 02 '20 at 09:14

6

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

edited Feb 02 '20 at 09:14

Community

1
1

answered Jan 07 '20 at 06:59

ebuzz168

922
1
12
30

score 4 · Answer 5 · edited Jun 27 '18 at 20:35

4

Try the following:

SELECT city 
FROM station 
WHERE left(city,1) not regexp 'a|e|i|o|u' or right(city,1) not regexp 'a|e|i|o|u' 
GROUP BY city

edited Jun 27 '18 at 20:35

JSelser

3,351
1
19
36

answered Jun 27 '18 at 15:33

Samrat Rai

41
2

AB Abhi · Answer 6 · 2018-08-22T06:04:34.307

4

This SQL Query will helpful for you if you are using Oracle:

SELECT DISTINCT city
FROM station
WHERE regexp_like (city, '^[^aeiouAEIOU].*') 
OR regexp_like (city, '.*[^aeiouAEIOU]$');

Another answer if you are using Oracle:

SELECT DISTINCT (CITY) 
FROM STATION 
WHERE NOT regexp_like(lower(CITY),'^[aeiou].*[aeiou]$');

edited Aug 22 '18 at 06:04

answered Aug 22 '18 at 05:59

AB Abhi

2,476
22
30

Regarding '^[aeiou].*[aeiou]$', how to understand the comma and star here? I saw this is a popular answer, but now sure how to understand the .* – emily_relax Feb 09 '21 at 05:05
1. there is no comma, kindly check. 2. `.*` means string should start from either of (aeiou) and can have zero or more iteration of that letter. – AB Abhi Feb 17 '21 at 14:49

score 4 · Answer 7 · edited Oct 27 '20 at 20:51

4

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.

edited Oct 27 '20 at 20:51

Furkan Ekinci

2,384
3
29
38

answered Jul 27 '19 at 07:11

Anil Sapkota

37
2

`DISTINCT` is not a function, it's a _set quantifier_. Simply skip those extra parentheses and write `SELECT DISTINCT CITY FROM...`, to make code clearer. – jarlh Dec 28 '21 at 13:08

score 3 · Answer 8 · answered Feb 14 '19 at 16:31

Try this:

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%')
OR (CITY NOT LIKE '%a' AND
    CITY  NOT LIKE '%e' AND
    CITY NOT LIKE '%i' AND 
    CITY NOT LIKE '%o' AND 
    CITY NOT LIKE '%u')

score 3 · Answer 9 · answered Jun 03 '19 at 12:35

3

Oracle solution:

SELECT DISTINCT CITY FROM STATION WHERE 
NOT REGEXP_LIKE(UPPER(CITY), '^[AEIOU]') OR 
NOT REGEXP_LIKE(UPPER(CITY), '[AEIOU]$');

answered Jun 03 '19 at 12:35

nglauber

9,971
2
44
50

score 3 · Answer 10 · edited Aug 25 '19 at 18:24

3

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

this is what you are looking for bro.

edited Aug 25 '19 at 18:24

Butiri Dan

1,760
5
11
18

answered Aug 25 '19 at 17:11

Raj Singh

318
1
5
15

score 3 · Answer 11 · edited Mar 31 '21 at 15:31

3

This is a required solution for MS SQL SERVER,

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

edited Mar 31 '21 at 15:31

shyamzzp

117
7

answered Oct 08 '19 at 20:41

karthik8fd

41
2

score 3 · Answer 12 · answered Nov 22 '19 at 17:14

3

Try this:

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

answered Nov 22 '19 at 17:14

Mina Wissa

10,782
13
91
153

score 2 · Answer 13 · edited Sep 03 '20 at 07:07

2

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.

edited Sep 03 '20 at 07:07

Neo Anderson

4,778
2
9
27

answered Sep 02 '20 at 07:56

Rohan Aher

41
4

score 2 · Answer 14 · answered Dec 12 '20 at 12:33

2

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

answered Dec 12 '20 at 12:33

Neeraj Kumar

79
5

score 1 · Answer 15 · answered Jan 15 '19 at 16:55

1

I worked with this

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%'
AND CITY NOT LIKE '%a' 
AND CITY NOT LIKE '%e'
AND CITY NOT LIKE '%i'
AND CITY NOT LIKE '%o'
AND CITY NOT LIKE '%u'

answered Jan 15 '19 at 16:55

Kashif Faraz Shamsi

503
1
7
21

In the question, It asks OR not AND ".. either do not start with vowels OR do not end with vowels." – M. John Mar 10 '22 at 21:30

score 1 · Answer 16 · edited Jun 09 '19 at 13:45

1

select distinct CITY
from STATION
where SUBSTRING(CITY, Length(CITY), 1) NOT in ('a','e','i','o','u','A','E','I','O','U') 
order by CITY

edited Jun 09 '19 at 13:45

Nolequen

2,412
6
33
50

answered Jan 22 '19 at 06:21

srinidhi sundarrajan

11
2

score 1 · Answer 17 · edited Jun 09 '19 at 11:32

1

Select Distinct
       City
From
       Station
Where
       Lower(Left(City,1)) not in ('a','e','i','o','u')
       or Lower(Right(City,1)) not in ('a','e','i','o','u')

edited Jun 09 '19 at 11:32

Nolequen

2,412
6
33
50

answered Jan 29 '19 at 12:39

Александр Толмачев

11
2

1

Please explain why this is a valid answer and format the code. – Safiyya Jan 29 '19 at 12:42

score 1 · Answer 18 · edited Jan 30 '19 at 10:15

1

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

edited Jan 30 '19 at 10:15

Enea Dume

2,814
3
18
34

answered Jan 30 '19 at 10:03

user10989536

11
1

2

please, the answer it would be more completed if you add some explanation text for your solution. – Enea Dume Jan 30 '19 at 10:09

score 1 · Answer 19 · edited Apr 30 '19 at 12:37

1

This works for me

SELECT DISTINCT CITY FROM STATION WHERE NOT CITY RLIKE '^[AEIOUaeiou]' AND NOT CITY RLIKE '.*[AEIOUaeiou]$'

edited Apr 30 '19 at 12:37

vimuth

4,137
13
61
97

answered Apr 30 '19 at 12:17

Sireesha Pandeti

11
1

score 1 · Answer 20 · edited Apr 30 '19 at 17:01

1

mySQL query:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]' OR CITY REGEXP '[^aeiou]$'

edited Apr 30 '19 at 17:01

Zoe stands with Ukraine

25,310
18
114
149

answered Apr 30 '19 at 16:46

Raja Duraisamy

13
2

score 1 · Answer 21 · edited Jun 11 '19 at 12:16

1

Right answer this ,

SELECT DISTINCT CITY FROM STATION
WHERE NOT CITY RLIKE '^[AEIOUaeiou]' AND NOT CITY RLIKE '.*[AEIOUaeiou]$';

edited Jun 11 '19 at 12:16

JIJOMON K.A

1,250
2
11
29

answered Jun 11 '19 at 11:02

sparkle ramani

132
8

score 1 · Answer 22 · answered Jun 13 '19 at 15:16

1

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

answered Jun 13 '19 at 15:16

Vignesh Yalax

15
3

Answers which contains only code are not considered as complete – Ivan Kaloyanov Jun 13 '19 at 15:36

score 1 · Answer 23 · answered Apr 12 '22 at 22:49

1

I use this and it works, its ORACLE SQL:

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

answered Apr 12 '22 at 22:49

Jorge Ruiz

11
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 13 '22 at 01:04

score 0 · Answer 24 · edited Jul 05 '20 at 15:33

0

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]**%**';

edited Jul 05 '20 at 15:33

XORG_99

178
3
15

answered Nov 01 '18 at 22:45

To filter out both the upper case beginning vowel and the lower case beginning vowel, shouldn't it be an `AND` instead of an `OR`? Or better yet, just combine the cases, e.g. `WHERE CITY NOT LIKE '[AaEeIiOoUu]%' OR CITY NOT LIKE '%[AaEeIiOoUu]'` – ergohack Nov 01 '18 at 23:22

score 0 · Answer 25 · answered Jul 06 '19 at 14:09

0

mysql query: SELECT DISTINCT city FROM station WHERE city NOT REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$';

answered Jul 06 '19 at 14:09

Deepak Dev

1

score 0 · Answer 26 · edited Jul 09 '19 at 15:51

0

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

edited Jul 09 '19 at 15:51

A. S. K.

2,199
12
22

answered Jul 09 '19 at 14:58

user11760403

1

score 0 · Answer 27 · edited Jul 11 '19 at 07:38

0

The following query should do what you want. It worked for me successfully!

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

edited Jul 11 '19 at 07:38

luator

4,152
3
27
47

answered Jul 11 '19 at 06:43

Renu Prakash

1

1

Avoid using all UPPERCASE for statements ! – MJoy Jul 11 '19 at 07:01

score 0 · Answer 28 · edited Jul 16 '19 at 04:15

0

This worked for me using MYSQL. I also grouped the results by city, which is not needed...just prettier:

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

edited Jul 16 '19 at 04:15

Book Of Zeus

48,853
18
173
169

answered Jul 16 '19 at 04:03

Tim Sawyer

9
1

score 0 · Answer 29 · edited Jul 28 '19 at 15:56

0

This will work the way you want:

SELECT city 
FROM station 
WHERE left(city,1) not regexp 'a|e|i|o|u' or right(city,1) not regexp 'a|e|i|o|u' 
GROUP BY city

edited Jul 28 '19 at 15:56

brasofilo

24,660
15
89
174

answered Jul 28 '19 at 14:44

Moeen Ul islam

11
4

Welcome to SO. You should add some explanation and also format your code (in this case with triple backticks and sql syntax hint). – m02ph3u5 Jul 28 '19 at 15:13

score 0 · Answer 30 · edited Aug 30 '19 at 19:52

0

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

edited Aug 30 '19 at 19:52

Paul Roub

35,848
27
79
88

answered Aug 30 '19 at 19:25

Nikhil Kurkure

67
4

score 0 · Answer 31 · answered Oct 03 '19 at 12:03

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]$';

score 0 · Answer 32 · answered Oct 17 '19 at 20:10

0

How about this one:

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

answered Oct 17 '19 at 20:10

Janis Janovskis

17
1

Welcome to stackoverflow! When providing answers, please add context explaining what is happening in your code. Answers that are only code are not considered complete. – brae Oct 17 '19 at 20:36

Amanda Lan · Answer 33 · 2019-11-21T04:37:05.890

0

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

edited Nov 21 '19 at 04:37

answered Nov 21 '19 at 04:25

Amanda Lan

1
1

Abhishek Shukla · Answer 34 · 2019-12-31T10:08:13.837

0

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

edited Dec 31 '19 at 10:08

answered Dec 31 '19 at 10:00

Abhishek Shukla

72
6

score 0 · Answer 35 · answered Jan 06 '20 at 07:58

0

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]$' ;

answered Jan 06 '20 at 07:58

user12660192

1

1

Welcome to SO! The first answer is identical to the accepted answer: https://stackoverflow.com/posts/49229048/revisions, and is enough to answer the question. The second answer doesn't answer the question of either don't start with vowels or don't end with vowels.Your answer only either repeats a previous answer or doesn't add extra useful information. – MBorg Jan 06 '20 at 08:14

score 0 · Answer 36 · edited Mar 08 '20 at 05:10

0

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].*$');

edited Mar 08 '20 at 05:10

James Z

12,104
10
27
43

answered Feb 07 '20 at 08:37

dnyaneshwar mogal

39
6

score 0 · Answer 37 · edited Feb 11 '20 at 10:07

0

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]'

edited Feb 11 '20 at 10:07

B--rian

5,011
10
34
73

answered Feb 11 '20 at 07:01

Nandhini

11
2

Sumeet Kumar · Answer 38 · 2020-06-16T19:28:48.503

0

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.

edited Jun 16 '20 at 19:28

answered Jun 13 '20 at 18:46

Sumeet Kumar

85
5

2

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation. – ysf Jun 15 '20 at 09:53

score 0 · Answer 39 · answered Sep 11 '20 at 17:28

0

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

answered Sep 11 '20 at 17:28

Vivek Gajbhiye

86
1
3

score 0 · Answer 40 · answered Apr 07 '21 at 15:48

0

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

answered Apr 07 '21 at 15:48

Shubham Parmar

115
1
12

score 0 · Answer 41 · answered Jul 06 '21 at 13:55

0

Simple MySql query can be like :

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

answered Jul 06 '21 at 13:55

Ruchir Dixit

55
7

score 0 · Answer 42 · answered Dec 27 '21 at 13:13

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

score 0 · Answer 43 · answered May 08 '22 at 06:32

0

for MySQL

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

answered May 08 '22 at 06:32

Shashank

253
2
13

score -1 · Answer 44 · edited Sep 14 '19 at 12:11

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

edited Sep 14 '19 at 12:11

Deepstop

3,314
2
6
20

answered Sep 14 '19 at 10:52

Navin Kumar

1
1

score -1 · Answer 45 · edited Nov 22 '19 at 15:32

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

edited Nov 22 '19 at 15:32

TT.

15,428
6
44
84

answered Nov 22 '19 at 15:07

vivek arya

1

3

Welcome to StackOverflow! Please [edit your question](https://stackoverflow.com/posts/58996949/edit) to include an explanation for your code. This question has more than *two dozen other answers*, many of which have good explanations. Without one on your answer, it's of comparatively low quality and will likely get downvoted or removed. Adding that explanation will help justify your answer's continued existence here. – Das_Geek Nov 22 '19 at 15:40

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

45 Answers45

Linked