CODEWITHSUNDEEP
sqloracle-database
user9892866
user9892866

Reputation:

output which has first and last letter as vowel(a,e,i,o,u)

I want output like. I have to print those city which has first and last character as vowel.

ashoke
eureka
onkar
okkko
uae
eau

to this I have written below query. any effective way to do the same.

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: 1

Views: 3346

Answers (4)

Kenan Yildirim
Kenan Yildirim

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

naveen kumar
naveen kumar

Reputation: 1

select distinct city from station where city like '%[a,e,i,o,u]' and city like '[a,e,i,o,u]%'

Upvotes: 0

Stephen
Stephen

Reputation: 11

This is a simple alternative answer to the question

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

Upvotes: 1

user5683823
user5683823

Reputation:

Assuming all the city names are all lower-case letters, you could do something like this:

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

substr(city, 1, 1) takes the substring of city starting at position 1 and of length 1 (meaning, just the first letter). substr(city, -1, 1) is very similar, just the position is different: -1 means first letter from the end of the string - so this will give you the last letter of the city name.

If city may have both upper and lower case letters, in the WHERE clause use lower(city) instead of city.

EDIT: By popular request, here is how the same can be done with regular expressions. There is no point in using a regular expression approach here though; the standard string functions (like SUBSTR) are almost certain to be much faster than anything based on regular expressions.

....
where regexp_like(city, '^(a|e|i|o|u).*(a|e|i|o|u)$', 'i')

(a|e|i|o|u) means exactly one of those characters. ^ means anchor at the beginning of the string, and similarly $ at the end of the string. Strictly speaking, this requires the city name to be at least two letters long; if one-letter city names are possible, this can be modified easily. (The SUBSTR approach would require no changes.)

The last argument, 'i', makes the regexp matching case insensitive (just in case that is needed).

Upvotes: 2

Related Questions