显示以元音开头和结尾的城市名称

下面是使用 REGEXP_LIKE

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

i 告诉Oracle进行不区分大小写的搜索。这使我们不再需要列出大写和小写元音。

你可以使用正则表达式,但是-你也可以使用 substr

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>

查询:

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