输出第一个和最后一个字母为元音(a,e,i,o,u) [英] 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' ;

推荐答案

假设所有城市名称都是小写字母，你可以这样做:

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) 取 city 的子字符串，从位置 1 开始，长度为 1(意思是，只是第一个字母).substr(city, -1, 1) 非常相似，只是位置不同:-1 表示从 end 开始的第一个字母字符串 - 所以这会给你城市名称的最后一个字母.

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.

如果 city 可能有大写和小写字母，在 WHERE 子句中使用 lower(city) 而不是 city.

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

编辑:根据流行的要求，这里是如何使用正则表达式完成相同的操作.不过，在这里使用正则表达式方法是没有意义的；标准字符串函数(如 SUBSTR)几乎肯定比任何基于正则表达式的函数都要快得多.

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) 表示这些字符中的一个.^ 表示字符串开头的锚点，类似的 $ 表示字符串末尾的锚点.严格来说，这要求城市名称至少有两个字母长；如果可以使用单字母城市名称，则可以轻松修改.(SUBSTR 方法不需要更改.)

(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.)

最后一个参数 'i' 使正则表达式匹配不区分大小写(以防万一).

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

这篇关于输出第一个和最后一个字母为元音(a,e,i,o,u)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！