Display city names which start and end with vowels-CodePudding

I have been trying to write a SQL query to get city names start and end with vowels only.

Code:

select distinct city 
from station 
where REGEXP_LIKE (city, '^(a|e|i|o|u).*(a|e|i|o|u)$');

This above query gives me the wrong answer. I am using Oracle.

CodePudding user response：

Here is a more concise way to write your query using REGEXP_LIKE:

SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(city, '^[aeiou].*[aeiou]$', 'i');

The third match parameter i tells Oracle to do a case insensitive search. This frees us from the need of listing out uppercase and lowercase vowels.

CodePudding user response：

You can use regular expressions, but - you can also use substr function. For example:

SQL> with city (name) as
  2    (select 'BOSTON' from dual union all
  3     select 'ALBUQUERQUE' from dual
  4    )
  5  select name,
  6    case when substr(upper(name), 1, 1) in ('A', 'E', 'I', 'O', 'U') and
  7              substr((name), -1)        in ('A', 'E', 'I', 'O', 'U')
  8         then 'OK'
  9         else 'Not OK'
 10    end as result_1,
 11    --
 12    case when regexp_like(name, '^[aeiou].*[aeiou]$', 'i') then 'OK'
 13         else 'Not OK'
 14    end as result_2
 15  from city;

NAME        RESULT_1   RESULT_2
----------- ---------- ----------
BOSTON      Not OK     Not OK
ALBUQUERQUE OK         OK

SQL>

CodePudding user response：

I am able to write answer for it

Query :

select distinct city from station where regex_like ( city,'^(a|e|i|o|u|A|E|I|O|U).*(a|e|i|o|u|A|E|I|O|U)$');