如何查找不是以元音开头和结尾的城市名称 [英] How to find the city name not starting and ending with a vowel

问题描述

我正在尝试从STATION查询不以元音开头也不以元音结尾的CITY名称列表.结果不能包含重复项.

I'm trying to query the list of CITY names from STATION that do not start with vowels and do not end with vowels. The result cannot contain duplicates.

起初我尝试过这个:

select distinct CITY 
from STATION 
where (CITY NOT LIKE 'A%' 
  or CITY NOT LIKE 'E%' 
  or CITY NOT LIKE 'I%' 
  or CITY NOT LIKE 'O%' 
  or CITY NOT LIKE 'U%')
and (CITY NOT LIKE '%a' 
  or CITY not like '%e'  
  or CITY not like '%i' 
  or CITY not like '%o' 
  or CITY not like '%u');

但是我没有得到正确的答案，因此没有进行搜索并编写以下代码，该代码也没有给出正确的答案:

But I did not get the right answer so searched and code the following code which also does not give the right answer:

select distinct CITY 
from STATION 
where regexp_like(lower(CITY),'^[^aeiou].');

我希望仅不显示以元音开头和结尾的那些城市名称，但是在第一个查询中将显示所有城市名称，而在第二个查询中，不显示以元音开头的所有城市名称

I expect that only those city names which both start and end with a vowel should not be displayed but in the first query all the city names are being displayed and in the second one all the city names starting with a vowel are not displayed.

推荐答案

更容易阅读的是:

    SELECT DISTINCT  CITY 
    FROM STATION 
    WHERE  SUBSTR(CITY,1,1) NOT IN ('A','E','I','O','U')
    AND SUBSTR(CITY,-1,1) NOT IN ('A','E','I','O','U');

请注意第二个WHERE子句中的-1，该子句告诉Oracle从字符串末尾开始搜索.

Note the -1 in the second WHERE clause which tells Oracle to search from the end of the string.

这篇关于如何查找不是以元音开头和结尾的城市名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！