Query city names starting and ending with vowels

user_8266852 0
Viewed 12878

I wrote this code (db2) and it works just fine, but I'm wondering, is there a shorter way to write this?

Select Distinct city
From   station
Where  city Like 'A%a'
       Or city Like 'A%e'
       Or city Like 'A%i'
       Or city Like 'A%o'
       Or city Like 'A%u'
       Or city Like 'E%a'
       Or city Like 'E%e'
       Or city Like 'E%i'
       Or city Like 'E%o'
       Or city Like 'E%u'
       Or city Like 'I%a'
       Or city Like 'I%e'
       Or city Like 'I%i'
       Or city Like 'I%o'
       Or city Like 'I%u'
       Or city Like 'O%a'
       Or city Like 'O%e'
       Or city Like 'O%i'
       Or city Like 'O%o'
       Or city Like 'O%u'
       Or city Like 'U%a'
       Or city Like 'U%e'
       Or city Like 'U%i'
       Or city Like 'U%o'
       Or city Like 'U%u';
sql
16 Answers

You can use REGEXP in MySQL to operate Regular Expression

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

For people who don't familiar with Regular Expression

^[aeiou]    // Start with a vowel
.*          // Any characters at any times
[aeiou]$    // End with a vowel
user_66329230 
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^aeiouAEIOU].*');
user_35037010

Try the following in Oracle:

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

This will be answerable in SQL Server in 3 lines with an AND clause-

SELECT DISTINCT CITY FROM *TableName* WHERE
(city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%') 
AND 
(city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u');

...

Also, you can use Regex in case of MySQL as-

SELECT DISTINCT city
FROM   station
WHERE  city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'
user_128138150 
select distinct city from station where REGEXP_LIKE(city,'[aeiou]$');
user_156917890

For Oracle, you can write this-

SELECT * FROM(
    SELECT UNIQUE(city) FROM station WHERE
    LOWER(SUBSTR(city,1,1)) IN ('a','e','i','o','u')
    INTERSECT  
    SELECT UNIQUE(city) FROM station WHERE
    LOWER(SUBSTR(city,LENGTH(city),1)) IN ('a','e','i','o','u')
);
user_136530120 
SELECT DISTINCT CITY FROM STATION WHERE LOWER(LEFT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u') AND LOWER(RIGHT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u');
user_122896500

Maybe this code will work.

Select Distinct City from station
Where city Like '[aeiou]%[aeiou]'

This worked for me.

user_163040910

In MSSQL server code working fine

select distinct city from station where city like '[aeiouAEIOU]%[aeiouAEIOU]';
user_155127080 
select distinct CITY from STATION where substr(CITY,1,1)  in ('a','e','i','o','u') and substr(city,-1,1) in ('a','e','i','o','u');
user_192081540

This is working in my case

select distinct(city) from station where city LIKE '%e' or city LIKE '%a' or city LIKE '%i' or city LIKE '%o' or city LIKE '%u';
user_171764820
Related