SQL query to check if a name begins and ends with a vowel
MysqlSqlSelectMysql Problem Overview
I want to query the list of CITY
names from the table STATION(id, city, longitude, latitude)
which have vowels as both their first and last characters. The result cannot contain duplicates.
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?
Mysql Solutions
Solution 1 - Mysql
You could use a regular expression:
SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'
Solution 2 - Mysql
in Microsoft SQL server you can achieve this from below query:
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
> --Way 1 --It should work in all Oracle versions
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
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou].*[aeiou]');
> --Way 3 --it may fail in some versions of Oracle
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]', 'i');
Solution 3 - Mysql
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 name
column, so it will need to perform a full scan, as would
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]$'
Solution 4 - Mysql
You can try one simple solution for MySQL:
SELECT DISTINCT city FROM station WHERE city REGEXP "^[aeiou].*[aeiou]$";
Solution 5 - Mysql
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');
Solution 6 - Mysql
You could substring the first and last character and compare it with IN keyword,
WHERE SUBSTRING(NAME,1,1) IN (a,e,i,o,u) AND SUBSTRING(NAME,-1) IN (a,e,i,o,u)
Solution 7 - Mysql
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'
)
Solution 8 - Mysql
The below query will do for 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');
Solution 9 - Mysql
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
WHERE NOT city ~ '^[^aeiou]|[^aeiou]$'
WHERE NOT REGEXP_LIKE(city, '^[^aeiou]|[^aeiou]$')`
SQL Server
No regular expression support. Use LIKE
clause with square brackets:
WHERE city LIKE '[aeiou]%' AND city LIKE '%[aeiou]'
Solution 10 - Mysql
The below one worked for me in MySQL:
SELECT DISTINCT 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');
Solution 11 - Mysql
Try the following:
select distinct city
from station
where city like '%[aeuio]'and city like '[aeuio]%' Order by City;
Solution 12 - Mysql
I hope this will help
select distinct city from station where lower(substring(city,1,1)) in ('a','e','i','o','u') and lower(substring(city,length(city),length(city))) in ('a','e','i','o','u') ;
Solution 13 - Mysql
In Oracle:
SELECT DISTINCT city
FROM station
WHERE SUBSTR(lower(CITY),1,1) IN ('a','e','i','o','u') AND SUBSTR(lower(CITY),-1) IN ('a','e','i','o','u');
Solution 14 - Mysql
In MSSQL, this could be the way:
select distinct city from station
where
right(city,1) in ('a', 'e', 'i', 'o','u') and left(city,1) in ('a', 'e', 'i', 'o','u')
Solution 15 - Mysql
Try this for beginning with vowel
Oracle:
select distinct *field* from *tablename* where SUBSTR(*sort field*,1,1) IN('A','E','I','O','U') Order by *Sort Field*;
Solution 16 - Mysql
For MS access or MYSQL server
SELECT city FROM station
WHERE City LIKE '[aeiou]%'and City LIKE '%[aeiou]';
Solution 17 - Mysql
you can also do a hard code like this, where you are checking each and every case possible, it's easy to understand for beginners
SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE 'A%A' OR CITY LIKE 'E%E' OR CITY LIKE 'I%I' OR CITY LIKE 'O%O' OR
CITY LIKE 'U%U' 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%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%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%U' OR CITY LIKE 'U%A' OR CITY LIKE 'U%E' OR CITY LIKE 'U%I' OR
CITY LIKE 'U%O'
Solution 18 - Mysql
You can use LEFT()
and RIGHT()
functions. Left(CITY,1)
will get the first character of CITY
from left. Right(CITY,1)
will get the first character of CITY
from right (last character of CITY
).
DISTINCT
is used to remove duplicates. To make the comparison case-insensitive, we will use the LOWER()
function.
SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(LEFT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u') AND
LOWER(RIGHT(CITY,1)) IN ('a', 'e', 'i', 'o', 'u')
Solution 19 - Mysql
Try this below code,
SELECT DISTINCT CITY
FROM STATIOn
WHERE city RLIKE '^[aeiouAEIOU].*.[aeiouAEIOU]$'
Solution 20 - Mysql
select distinct(city) from STATION
where lower(substr(city, -1)) in ('a','e','i','o','u')
and lower(substr(city, 1,1)) in ('a','e','i','o','u');
Solution 21 - Mysql
Both of the statements below work in Microsoft SQL SERVER
SELECT DISTINCT
city
FROM
station
WHERE
SUBSTRING(lower(CITY), 1, 1) IN ('a', 'e', 'i', 'o', 'u')
AND SUBSTRING(lower(CITY), LEN(CITY), 1) IN ('a', 'e', 'i', 'o', 'u');
SELECT DISTINCT
City
FROM
Station
WHERE
City LIKE '[A, E, O, U, I]%[A, E, O, U, I]'
ORDER BY
City;
Solution 22 - Mysql
SELECT DISTINCT city
FROM station
WHERE city RLIKE '^[^aeiouAEIOU]'OR city RLIKE'[^aeiouAEIOU]$'
Solution 23 - Mysql
Try the following:
select distinct city from station where city REGEXP '^[aeiou]' and city REGEXP '[aeiou]$';
Solution 24 - Mysql
For oracle :
SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(city, '^[aeiou].*[aeiou]$','i') ;
Solution 25 - Mysql
My simple solution
SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[a,e,i,o,u]%[a,e,i,o,u]';
Solution 26 - Mysql
Worked for me using simple left, right functions in MS SQL
select city from station where left(city,1) in ('a','e','i','o','u') and right(city,1) in ('a','e','i','o','u')
Solution 27 - Mysql
SELECT DISTINCT CITY From STATION WHERE LOWER(SUBSTR(CITY,1,1)) IN ('a','e','i','o','u');
this will work in my sql
Solution 28 - Mysql
This works on oracle:
select distinct COLUMN_NAME
from TABLE_NAME
where
--convert first character and check for vowel
lower(substr(COLUMN_NAME, 1, 1)) IN ('a', 'e', 'i', 'o', 'u')
and
--convert last character and check for vowel
lower(substr(COLUMN_NAME, -1, 1)) IN ('a', 'e', 'i', 'o', 'u')
order by COLUMN_NAME asc;
Solution 29 - Mysql
If you are using Sequal server management studio (SSMS)
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[a,e,i,o,u]%[a,e,i,o,u]';
Solution 30 - Mysql
This works in MYSQL:
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[a,e,i,o,u]' AND
CITY REGEXP '[a,e,i,o,u]$';
Solution 31 - Mysql
Try this as I tried and it worked for me.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou]' AND CITY REGEXP '[aeiou]$';
Solution 32 - Mysql
FOR Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.
SELECT MY SQL AS the Preferred Engine
SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP '^[AEIOUaeiou]' AND CITY REGEXP '[AEIOUaeiou]$' ;
Very simple answer.