CODEWITHSUNDEEP
sql
cristo spreeuwers
cristo spreeuwers

Reputation: 51

Query city names starting and ending with vowels

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

Upvotes: 5

Views: 21162

Answers (17)

Sunny Kumar
Sunny Kumar

Reputation: 33

I solved this with the query,

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

Upvotes: 0

Aamir Mulla
Aamir Mulla

Reputation: 76

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

Upvotes: 0

Venkatesh Dhanasekaran
Venkatesh Dhanasekaran

Reputation: 344

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

Upvotes: 0

Siddharth Singhal
Siddharth Singhal

Reputation: 1

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

Upvotes: 0

Venkatesh Dhanasekaran
Venkatesh Dhanasekaran

Reputation: 344

Try the following in Oracle:

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

Upvotes: 0

Shivam Baldha
Shivam Baldha

Reputation: 56

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

Upvotes: 0

jenish patel
jenish patel

Reputation: 11

In MSSQL server code working fine

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

Upvotes: 1

Hardik Parmar
Hardik Parmar

Reputation: 11

Maybe this code will work.

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

This worked for me.

Upvotes: 0

Er Ankit Sukhija
Er Ankit Sukhija

Reputation: 1

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

Upvotes: 0

Mahedi Kamal
Mahedi Kamal

Reputation: 189

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

Upvotes: 0

Devyani Kharecha
Devyani Kharecha

Reputation: 16

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

Upvotes: 0

Varun Tiwari
Varun Tiwari

Reputation: 11

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

Upvotes: 1

Tzu7
Tzu7

Reputation: 73

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

Upvotes: 1

simran kumari
simran kumari

Reputation: 21

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

Upvotes: 0

Dalila Cook
Dalila Cook

Reputation: 11

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

Upvotes: -1

fhossfel
fhossfel

Reputation: 2191

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

Upvotes: 5

Max Hanglin
Max Hanglin

Reputation: 306

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

Upvotes: 0

Related Questions