MySQL 用于检查名称是否以元音开头和结尾的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36627613/
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
SQL query to check if a name begins and ends with a vowel
提问by Zeus
I want to query the list of CITYnames from the table STATION(id, city, longitude, latitude)which have vowels as both their first and last characters. The result cannot contain duplicates.
我想CITY从表中查询以STATION(id, city, longitude, latitude)元音作为第一个和最后一个字符的名称列表。结果不能包含重复项。
For this is I wrote a query like WHERE NAME LIKE 'a%'that had 25 conditions, each vowel for every other vowel, which is quite unwieldy. Is there a better way to do it?
为此，我写了一个这样的查询WHERE NAME LIKE 'a%'，它有 25 个条件，每个元音对应其他每个元音，这非常笨拙。有没有更好的方法来做到这一点？
回答by Mureinik
You could use a regular expression:
您可以使用正则表达式：
SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'
回答by Banketeshvar Narayan
in Microsoft SQL serveryou can achieve this from below query:
在Microsoft SQL 服务器中，您可以通过以下查询实现此目的：
SELECT distinct City FROM STATION WHERE City LIKE '[AEIOU]%[AEIOU]'
Or
或者
SELECT distinct City FROM STATION WHERE City LIKE '[A,E,I,O,U]%[A,E,I,O,U]'
Update --Added Oracle Query
更新——添加了 Oracle 查询
--Way 1 --It should work in all Oracle versions
--方式1 --它应该适用于所有Oracle版本
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou]') and REGEXP_LIKE(LOWER(CITY), '[aeiou]$');
--Way 2 --it may fail in some versions of Oracle
--方式 2 --在某些版本的 Oracle 中可能会失败
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou].*[aeiou]');
--Way 3 --it may fail in some versions of Oracle
--方式3 --在某些版本的Oracle中可能会失败
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]', 'i');
回答by Barmar
Use a regular expression.
使用正则表达式。
WHERE name REGEXP '^[aeiou].*[aeiou]$'
^and $anchor the match to the beginning and end of the value.
^并将$匹配锚定到值的开头和结尾。
In my test, this won't use an index on the namecolumn, so it will need to perform a full scan, as would
在我的测试中，这不会在name列上使用索引，因此需要执行完整扫描，就像
WHERE name LIKE 'a%a' OR name LIKE 'a%e' ...
I think to make it use an index you'd need to use a union of queries that each test the first letter.
我认为要使其使用索引，您需要使用每个测试第一个字母的查询联合。
SELECT * FROM table
WHERE name LIKE 'a%' AND name REGEXP '[aeiou]$'
UNION
SELECT * FROM table
WHERE name LIKE 'e%' AND name REGEXP '[aeiou]$'
UNION
SELECT * FROM table
WHERE name LIKE 'i%' AND name REGEXP '[aeiou]$'
UNION
SELECT * FROM table
WHERE name LIKE 'o%' AND name REGEXP '[aeiou]$'
UNION
SELECT * FROM table
WHERE name LIKE 'u%' AND name REGEXP '[aeiou]$'
回答by Ganesh Giri
You can try one simple solution for MySQL:
您可以为 MySQL 尝试一种简单的解决方案：
SELECT DISTINCT city FROM station WHERE city REGEXP "^[aeiou].*";
回答by ogres
You could substring the first and last character and compare it with IN keyword,
您可以对第一个和最后一个字符进行子字符串化并将其与 IN 关键字进行比较，
WHERE SUBSTRING(NAME,1,1) IN (a,e,i,o,u) AND SUBSTRING(NAME,-1) IN (a,e,i,o,u)
回答by Suman
You may try this
你可以试试这个
select city
from station where SUBSTRING(city,1,1) in ('A','E','I','O','U') and
SUBSTRING(city,-1,1) in ('A','E','I','O','U');
回答by shivanshu dhawan
The below query will do for Orale DB:
以下查询适用于 Orale DB：
select distinct(city) from station where upper(substr(city, 1,1)) in ('A','E','I','O','U') and upper(substr(city, length(city),1)) in ('A','E','I','O','U');
回答by Pankaj Pathak
SELECT distinct CITY
FROM STATION
where (CITY LIKE 'a%'
OR CITY LIKE 'e%'
OR CITY LIKE 'i%'
OR CITY LIKE 'o%'
OR CITY LIKE 'u%'
) AND (CITY LIKE '%a'
OR CITY LIKE '%e'
OR CITY LIKE '%i'
OR CITY LIKE '%o'
OR CITY LIKE '%u'
)
回答by abhinay vijay
Try the following:
请尝试以下操作：
select distinct city
from station
where city like '%[aeuio]'and city like '[aeuio]%' Order by City;
回答by Salman A
You can use the following regular expression and invert the result:
您可以使用以下正则表达式并反转结果：
^[^aeiou]|[^aeiou]$
This works even if the input consists of a single character. It should work across different regex engines.
即使输入由单个字符组成，这也有效。它应该适用于不同的正则表达式引擎。
SELECT city
FROM (
SELECT 'xx' AS city UNION
SELECT 'ax' UNION
SELECT 'xa' UNION
SELECT 'aa' UNION
SELECT 'x' UNION
SELECT 'a'
) AS station
WHERE NOT city REGEXP '^[^aeiou]|[^aeiou]$'
PostgreSQL
PostgreSQL
WHERE NOT city ~ '^[^aeiou]|[^aeiou]$'
WHERE NOT REGEXP_LIKE(city, '^[^aeiou]|[^aeiou]$')`
SQL Server
数据库服务器
No regular expression support. Use LIKEclause with square brackets:
不支持正则表达式。LIKE带方括号的Use子句：
WHERE city LIKE '[aeiou]%' AND city LIKE '%[aeiou]'
