CODEWITHSUNDEEP
sqloracle-database
Varun K R
Varun K R

Reputation: 55

In SQL I want to display the names of all those cities which end with an vowel

I wrote the following query

SELECT UNIQUE 
    CITY 
FROM 
    STATION 
WHERE 
    (CITY LIKE '%A' OR 
     CITY LIKE '%E' OR 
     CITY LIKE '%I' OR 
     CITY LIKE '%O' OR 
     CITY LIKE '%U') 
ORDER BY 
    CITY;

What is wrong with this?

Upvotes: 5

Views: 30820

Answers (18)

Mandillah Jr
Mandillah Jr

Reputation: 1

SELECT UNIQUE CITY 
FROM STATION 
WHERE UPPER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('A','E','I','O','U');

Upvotes: 0

Sunku Vamsi Tharun Kumar
Sunku Vamsi Tharun Kumar

Reputation: 423

SELECT
   Distinct(CITY)
FROM
   STATION
WHERE
   UPPER(SUBSTR(CITY,1,1)) IN ('A','E','I','O','U');

It is working in MySQL.

Upvotes: 0

Gowtham Prasath
Gowtham Prasath

Reputation: 21

The simplest is using regular expression:

SELECT 
  CITY 
FROM 
  STATION 
WHERE 
  CITY RLIKE "[aeiouAEIOU]$";

Upvotes: 0

RKP
RKP

Reputation: 49

I think this 

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

Upvotes: 0

Yasin
Yasin

Reputation: 124

Use regular expression

SELECT DISTINCT(name) FROM city WHERE LOWER(name) RLIKE '.*[aeiou]$'

Upvotes: 0

Joginder Pawan
Joginder Pawan

Reputation: 689

Below solution works for Oracle DB:

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

If you do not use UPPER, then your test cases in which city name is starting with lower case letter will fail.

Upvotes: 1

DM14
DM14

Reputation: 307

It seems to me that it will be easier to do it this way:

SELECT DISTINCT CITY 
FROM STATION  
WHERE CITY LIKE '%[AaEeIiOoUu]'*

Upvotes: 2

McClAnalytics
McClAnalytics

Reputation: 330

This worked for me (Oracle 11g):

select distinct
    s.city
from
    station s
where 
    upper(substr(s.city,-1,1)) in ('A','E','I','O','U');

Upvotes: 4

Yagjna Kurra
Yagjna Kurra

Reputation: 1

You may use this code for your purpose!!

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

Upvotes: 0

Walter White
Walter White

Reputation: 1026

In MySql

    select distinct city 
    from Station 
    where ( city regexp '.[aeiou]$' );

Upvotes: 0

Surya Pratap
Surya Pratap

Reputation: 11

For Oracle 

select DISTINCT(CITY) from STATION where upper(SUBSTR(CITY,-1,1)) IN ('A','I','O','E','U');

Upvotes: -1

Ashwini M
Ashwini M

Reputation: 149

In SQL Server this could be other version of the answer

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

Upvotes: -1

Haritha Yalavarthi
Haritha Yalavarthi

Reputation: 187

We can supply another parameter to regexp to handle case sensitivity:

  • i - case insensitive,
  • c - case sensitive

For example:

select distinct city 
from station 
where regexp_like(city,'[aeiou]$','i')
order by city

Upvotes: 2

prasad
prasad

Reputation: 31

SELECT UNIQUE CITY FROM STATION 
WHERE 
    (CITY LIKE 'A%' OR 
     CITY LIKE 'E%' OR
     CITY LIKE 'I%' OR 
     CITY LIKE 'O%' OR 
     CITY LIKE 'U%');

% wild card character should come after the alphabet

Upvotes: 0

Prosen Ghosh
Prosen Ghosh

Reputation: 655

Use upper function.This function will help you to capitalize the letter of your text.

SELECT DISTINCT CITY 
FROM   STATION 
WHERE  UPPER(CITY) LIKE '%A' 
   OR  UPPER(CITY) LIKE '%E' 
   OR  UPPER(CITY) LIKE '%I' 
   OR  UPPER(CITY) LIKE '%O' 
   OR  UPPER(CITY) LIKE '%U';

Upvotes: 1

Francois
Francois

Reputation: 524

If you want it to work with lower/upper letters, you could use UPPER(CITY), otherwise it's all good.

Upvotes: 4

shA.t
shA.t

Reputation: 16958

I think you can use REGEXP_LIKE like this:

SELECT UNIQUE CITY 
FROM STATION 
WHERE REGEXP_LIKE(CITY, '[AaEeIiOoUu]$') 
ORDER BY CITY;

Upvotes: 9

Mahmoud Abd El Samea
Mahmoud Abd El Samea

Reputation: 47

If you are using MySQL try this solution:

SELECT UNIQUE CITY 
FROM STATION 
WHERE (CITY REGEXP '%A$' OR CITY REGEXP '%E$' OR CITY REGEXP '%I$' OR CITY REGEXP '%O$' OR CITY REGEXP '%U$') 
ORDER BY CITY;

Upvotes: -2

Related Questions