MySQL SQL 查询以查找不以元音开头的城市名称列表

SQL query to find a list of city names that dont start with vowels

提问by Zeus

I'm trying to query the list of CITY names from table - STATION that do not start with vowels with results that cannot contain duplicates. The table just has id, city, population

我正在尝试从表中查询城市名称列表 - STATION 不以元音开头，结果不能包含重复项。该表只有 id、城市、人口

This is the query that I've written

这是我写的查询

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '[^bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ].*';

This gives a wrong answer. What am I doing wrong here?

这给出了错误的答案。我在这里做错了什么？

回答by Tin Tran

try this.

尝试这个。

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT RLIKE '^[aeiouAEIOU].*$'

回答by Arjan

A ^in regular expressions can have different meanings, depending on its location. When it's the first character in a regex, it refers to the start of the string. But when it's the first character in a set, like [^abc], it means not one of. And when it appears elsewhere, it just refers to the ^itself.

^正则表达式中的A可以具有不同的含义，具体取决于其位置。当它是正则表达式中的第一个字符时，它指的是字符串的开头。但是当它是集合中的第一个字符时，例如[^abc]，则表示not one of。当它出现在别处时，它只是指它^自己。

So you would need something like:

所以你需要类似的东西：

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '^[bcdfghjklmnpqrstvwxyzBCDFGHJKLMNPQRSTVWXYZ].*';

or, just exclude the letters you don't want:

或者，只排除您不想要的字母：

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE '^[^aeiouAEIOU].*';

回答by satish

select distinct city from station 
where city Not like 'A%' and city Not like 'E%' and city Not like 'I%' and
      city Not like 'o%' and city not like 'U%';

回答by Rahul Yadav

Query the list of CITY names from STATIONthat either do not start with vowels or do not end with vowels. IN ORACLE

查询STATION不以元音开头或不以元音结尾的城市名称列表。在甲骨文

select distinct city 
from station 
where regexp_like(city, '^[^aeiouAEIOU]|*[^aeiouAEIOU]$');

In MySQL -

在 MySQL 中 -

 select distinct city 
 from station 
 where city RLIKE '^[^aeiouAEIOU].*' OR 
 city RLIKE '^.*[^aeiouAEIOU]$';

回答by Makhmud Galy

I tried this.

我试过这个。

select distinct city
from station
where substring(city,1,1) not in('A','E','I','O','U');

回答by simran kumari

Try the following:

请尝试以下操作：

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[^aeiou].*';

MySQL Ref.: https://dev.mysql.com/doc/refman/5.5/en/regexp.html

MySQL 参考：https: //dev.mysql.com/doc/refman/5.5/en/regexp.html

回答by ed lovejoy

Simple example without regular expressions

没有正则表达式的简单示例

SELECT DISTINCT 
    City 
FROM Station
WHERE LEFT(City, 1) NOT IN ("a", "e", "i", "o", "u");

回答by marwari

select distinct city from station where city regexp
'^[^aeiou].*[^aeiou]$'

If your query doesn't starts and ends with a vowel.

如果您的查询不以元音开头和结尾。

回答by Aman Verma

select DISTINCT CITY from STATION where CITY NOT LIKE '[a,e,i,o,u]%'

回答by Arun Solomon

Mysql we can use this.

mysql我们可以用这个。

select distinct city from station where city regexp '^[^aeiou].*';

To Know More About MySQL Regular Expression

了解有关 MySQL 正则表达式的更多信息

For Oracle we can use

对于 Oracle 我们可以使用

select distinct(city) from station where regexp_like (city, '^[^AEIOU](*)');

To Know More About Oracle Regular Expression

了解有关 Oracle 正则表达式的更多信息

For MS SQL Server

对于 MS SQL 服务器

select distinct city from station where city not like '[aeuio]%';