Query city names starting and ending with vowels

Question

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

Siyual, I think he needs this instead - LIKE '[AEIOU]%[aeiou]' — Aamir Mulla, Jul 06 '17 at 18:47

score 5 · Answer 1 · answered Jul 06 '17 at 18:49

I am not a DB2 expert but this should be fairly portable:

WHERE LEFT(city,1) IN ('A', 'E', 'I', 'O', 'U')
  AND RIGHT(city,1) IN ('a', 'e', 'i', 'o', 'u')

You may want to normalize it all to upper case to avoid problems with cities that for some reason start with a lower case letter or end with an upper case letter.

WHERE UPPER(LEFT(city,1)) IN ('A', 'E', 'I', 'O', 'U')
  AND LOWER(RIGHT(city,1)) IN ('a', 'e', 'i', 'o', 'u')

Tzu7 · Answer 2 · 2020-08-14T08:19:46.720

2

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

edited Aug 14 '20 at 08:19

answered Aug 14 '20 at 07:50

Tzu7

73
6

score 1 · Answer 3 · edited Aug 16 '20 at 13:01

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

score 0 · Answer 4 · answered Jul 06 '17 at 18:43

I'm not an expert in DB2, but I think you can use a regular expression in your WHERE LIKE.

Check the documentation: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061494.html

Example:
Select Distinct city
From   station
Where  REGEXP_LIKE(city,'[aeiou].*[aeiou]')

score 0 · Answer 5 · edited Jul 04 '22 at 20:11

0

Use regular expressions in your LIKE clause. Look at the example here Using RegEx in SQL Server

In Sql server your LIKE clause might look like this.

LIKE '[AEIOU]%[aeiou]'

In DB2 the syntax is slightly different. Look few examples down on this page.

https://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html

edited Jul 04 '22 at 20:11

Dharman

30,962
25
85
135

answered Jul 06 '17 at 18:45

Aamir Mulla

76
1
6

score 0 · Answer 6 · edited Feb 21 '18 at 16:48

0

You could use substr like this:

SELECT DISTINCT CITY 
FROM STATION WHERE SUBSTR(CITY,1,1) IN('A','E','I','O','U','a','e','i','o','u') 
and SUBSTR(CITY,-1,1) IN('A','E','I','O','U','a','e','i','o','u');

edited Feb 21 '18 at 16:48

trincot

317,000
35
244
286

answered Feb 21 '18 at 16:39

simran kumari

21
1

score 0 · Answer 7 · edited Jul 04 '22 at 20:11

0

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

edited Jul 04 '22 at 20:11

Dharman

30,962
25
85
135

answered May 22 '18 at 04:34

Venkatesh Dhanasekaran

344
2
7

score 0 · Answer 8 · edited Jul 04 '22 at 20:11

0

Try the following in Oracle:

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

edited Jul 04 '22 at 20:11

Dharman

30,962
25
85
135

answered Feb 26 '19 at 05:38

Venkatesh Dhanasekaran

344
2
7

score 0 · Answer 9 · edited Apr 19 '21 at 11:01

0

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

edited Apr 19 '21 at 11:01

Syscall

19,327
10
37
52

answered Apr 19 '21 at 10:14

Devyani Kharecha

16
3

Seem almost similar to [this answer](https://stackoverflow.com/a/63408714/9193372) – Syscall Apr 19 '21 at 11:02

score 0 · Answer 10 · answered Jul 07 '21 at 14:32

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

score 0 · Answer 11 · edited Mar 24 '22 at 05:36

0

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

edited Mar 24 '22 at 05:36

Suraj Rao

29,388
11
94
103

answered Mar 24 '22 at 05:35

Er Ankit Sukhija

1
1

score 0 · Answer 12 · edited Apr 11 '22 at 17:20

0

Maybe this code will work.

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

This worked for me.

edited Apr 11 '22 at 17:20

cigien

57,834
11
73
112

answered Apr 11 '22 at 14:33

Hardik Parmar

11
2

score 0 · Answer 13 · edited May 23 '22 at 00:24

0

In MSSQL server code working fine

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

edited May 23 '22 at 00:24

bunyaminkirmizi

540
2
6
22

answered May 21 '22 at 17:38

jenish patel

1

score 0 · Answer 14 · edited Jul 04 '22 at 20:11

0

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

edited Jul 04 '22 at 20:11

Dharman

30,962
25
85
135

answered May 26 '22 at 16:05

Siddharth Singhal

1

score 0 · Answer 15 · answered Jun 28 '22 at 11:47

0

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

answered Jun 28 '22 at 11:47

Shivam Baldha

48
8

1st `distinct` is not a function. 2nd What does this answer add to all other answers. – Shmiel Jul 03 '22 at 18:01

score 0 · Answer 16 · answered Jan 11 '23 at 12:15

0

I solved this with the query,

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

answered Jan 11 '23 at 12:15

Sunny Kumar

33
5

score -1 · Answer 17 · answered Aug 23 '17 at 15:44

-1

As a beginner, this is how I solved it:

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

answered Aug 23 '17 at 15:44

Dalila Cook

11

Query city names starting and ending with vowels

17 Answers17