在 SQL 中，我想显示所有以元音结尾的城市的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32284793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In SQL I want to display the names of all those cities which end with an vowel
提问by Varun K R
I wrote the following query
我写了以下查询
SELECT UNIQUE
CITY
FROM
STATION
WHERE
(CITY LIKE '%A' OR
CITY LIKE '%E' OR
CITY LIKE '%I' OR
CITY LIKE '%O' OR
CITY LIKE '%U')
ORDER BY
CITY;
What is wrong with this?
这有什么问题？
采纳答案by Francois
If you want it to work with lower/upper letters, you could use UPPER(CITY), otherwise it's all good.
如果您希望它使用小写/大写字母，则可以使用UPPER(CITY)，否则一切都很好。
回答by shA.t
I think you can use REGEXP_LIKElike this:
我认为你可以这样使用REGEXP_LIKE：
SELECT UNIQUE CITY
FROM STATION
WHERE REGEXP_LIKE(CITY, '[AaEeIiOoUu]$')
ORDER BY CITY;
回答by McClAnalytics
This worked for me (Oracle 11g):
这对我有用（Oracle 11g）：
select distinct
s.city
from
station s
where
upper(substr(s.city,-1,1)) in ('A','E','I','O','U');
回答by Haritha Yalavarthi
We can supply another parameter to regexpto handle case sensitivity:
我们可以提供另一个参数regexp来处理区分大小写：
i- case insensitive,c- case sensitive
i- 不区分大小写，c- 区分大小写
For example:
例如：
select distinct city
from station
where regexp_like(city,'[aeiou]$','i')
order by city
回答by DM14
It seems to me that it will be easier to do it this way:
在我看来，这样做会更容易：
SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '%[AaEeIiOoUu]'*
回答by Joginder Pawan
Below solution works for Oracle DB:
以下解决方案适用于 Oracle DB：
select distinct(city)
from station
where UPPER(substr(city,1,1))
in ('A','E','I','O','U');
If you do not use UPPER, then your test cases in which city name is starting with lower case letter will fail.
如果您不使用UPPER，那么您的城市名称以小写字母开头的测试用例将失败。
回答by Prosen Ghosh
Use upper function.This function will help you to capitalize the letter of your text.
使用 upper 函数。此函数将帮助您将文本的字母大写。
SELECT DISTINCT CITY
FROM STATION
WHERE UPPER(CITY) LIKE '%A'
OR UPPER(CITY) LIKE '%E'
OR UPPER(CITY) LIKE '%I'
OR UPPER(CITY) LIKE '%O'
OR UPPER(CITY) LIKE '%U';
回答by prasad
SELECT UNIQUE CITY FROM STATION
WHERE
(CITY LIKE 'A%' OR
CITY LIKE 'E%' OR
CITY LIKE 'I%' OR
CITY LIKE 'O%' OR
CITY LIKE 'U%');
% wild card character should come after the alphabet
% 通配符应该在字母之后
回答by Sunku Vamsi Tharun Kumar
SELECT
Distinct(CITY)
FROM
STATION
WHERE
UPPER(SUBSTR(CITY,1,1)) IN ('A','E','I','O','U');
It is working in MySQL.
它在 MySQL 中工作。
回答by Gowtham Prasath
The simplest is using regular expression:
最简单的是使用正则表达式：
SELECT
CITY
FROM
STATION
WHERE
CITY RLIKE "[aeiouAEIOU]$";
