Reputation: 2253

SQL query to find a list of city names that dont start with vowels

I'm trying to query the list of CITY names from table - STATION that do not start with vowels with results that cannot contain duplicates. The table just has id, city, population

This is the query that I've written

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '[^bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ].*';

This gives a wrong answer. What am I doing wrong here?

Upvotes: 12

Answers (26)

asif rikibdar

Reputation: 11

try this:

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT REGEXP '[aeiouAEIOU]$'

Upvotes: 1

gowtham rajeevswaroop

Reputation: 55

select distinct city from station where REGEXP_LIKE(LOWER(city),'^[^aeiou].*.[^aeiou]$');

[^aeiou] Does not accept aeiou.

Upvotes: 0

Ganesh Giri

Reputation: 1161

Try this one:

SELECT DISTINCT CITY FROM STATION WHERE
LOWER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('a','e','i','o','u');

Upvotes: 0

Aman Verma

Reputation: 51

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

Upvotes: 2

Sajal Singh

Reputation: 385

You can use:

select distinct city from station where city  not RLIKE '^[aeiouAEIOU]'

Upvotes: 0

Shamantha Krishna

Reputation: 35

SELECT DISTINCT city FROM station
WHERE CITY NOT REGEXP '^[aeiou]'

MySQL accepted answer.

Upvotes: 1

srinidhi sundarrajan

Reputation: 11

select distinct <COLUMN NAME> from <table name>
WHERE SUBSTRING(<COLUMN NAME>,1,1) not in ('a','e','i','o','u','A','E','I','O','U')
order by <COLUMN NAME>

Distinct words do not start with a vowel.

Upvotes: 0

Kashif Faraz Shamsi

Reputation: 511

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

Upvotes: 0

Makhmud Galy

Reputation: 61

I tried this.

select distinct city
from station
where substring(city,1,1) not in('A','E','I','O','U');

Upvotes: 4

Arun Solomon

Reputation: 421

Mysql we can use this.

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

To Know More About MySQL Regular Expression

For Oracle we can use

select distinct(city) from station where regexp_like (city, '^[^AEIOU](*)');

To Know More About Oracle Regular Expression

For MS SQL Server

select distinct city from station where city not like '[aeuio]%';

Upvotes: 1

Jay Ehsaniara

Reputation: 1529

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

distinct: to avoid the duplication on the following column

regexp: MySql function (regexp) to obtain Regular Expressions. expression starts with ^ and ends with $. In Oracle its regexp_like (city,'RegEx') Regular Expressions With Oracle Database.

[^...] means any character NOT contain any of ...
[^...]. means a single character NOT contain any of ...
[^...].* means first character NOT contain any of ...
[^...].*[^...] means first character NOT contains any of ... AND not ends with ...
[^aeiou].*[^aeiou] mean the first character NOT starts with vowels AND not ends with vowels

Upvotes: 0

Rasim SEN

Reputation: 21

for oracle

select distinct 
    city 
from station 
where regexp_like(city, '^[^aeiouAEIOU].*');

Upvotes: -1

ed lovejoy

Reputation: 11

Simple example without regular expressions

SELECT DISTINCT 
    City 
FROM Station
WHERE LEFT(City, 1) NOT IN ("a", "e", "i", "o", "u");

Upvotes: 1

Aaron W.

Reputation: 1

In ORACLE:

select 
distinct(city)
from station
where regexp_like (city, '^[^A|E|I|O|U](*)');

There is no need to specify the ending letter in this solution. It's not called out for in the problem presented.

Upvotes: 0

Rohit Rawat

Reputation: 1

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

David Osipov

Reputation: 1

 SELECT DISTINCT city from STATION
 WHERE city NOT LIKE "[aieuo]%[aieuo] [aieuo]%[aieuo]"
 AND city NOT LIKE "[aieuo]%[aieuo]";

The first NOT LIKE means, that each word of the two-word city names doesn't start and end with vowels. The second NOT LIKE is for one-word city names.

Upvotes: 0

Rahul Yadav

Reputation: 67

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. IN ORACLE

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

In MySQL -

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

Upvotes: 6

Darpan

Reputation: 1

Select Distinct City from Station where City Like '[^aeiou]%';

MS SQL Server Tested.

Upvotes: 0

simran kumari

Reputation: 21

Try the following:

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

MySQL Ref.: https://dev.mysql.com/doc/refman/5.5/en/regexp.html

Upvotes: 2

marwari

Reputation: 191

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

If your query doesn't starts and ends with a vowel.

Upvotes: 1

Krishneil

Reputation: 1490

In MS SQL:

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

Upvotes: 0

abhinay vijay

Reputation: 29

This worked on MS SQL SERVER:

select distinct city from station where city NOT like '[aeuio]%' Order by City;

Upvotes: 0

Saikrishna Syamala

Reputation: 1

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%"
));

This will work i guess.

Upvotes: -1

satish

Reputation: 71

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

Upvotes: 7

Arjan

Reputation: 9874

A ^ in regular expressions can have different meanings, depending on its location. When it's the first character in a regex, it refers to the start of the string. But when it's the first character in a set, like [^abc], it means not one of. And when it appears elsewhere, it just refers to the ^ itself.

So you would need something like:

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '^[bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ].*';

or, just exclude the letters you don't want:

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '^[^aeiouAEIOU].*';

Upvotes: 13

Tin Tran

Reputation: 6202

try this.

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

Upvotes: 29

SQL query to find a list of city names that dont start with vowels

Answers (26)

Related Questions