SQL query to check if a name begins and ends with a vowel

MysqlSqlSelect

Mysql 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.

MySQL

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]$'

Oracle

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)the images shows that we are getting what exactly you have asked

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionZeusView Question on Stackoverflow
Solution 1 - MysqlMureinikView Answer on Stackoverflow
Solution 2 - MysqlBanketeshvar NarayanView Answer on Stackoverflow
Solution 3 - MysqlBarmarView Answer on Stackoverflow
Solution 4 - MysqlGanesh GiriView Answer on Stackoverflow
Solution 5 - MysqlSumanView Answer on Stackoverflow
Solution 6 - MysqlogresView Answer on Stackoverflow
Solution 7 - MysqlPankaj PathakView Answer on Stackoverflow
Solution 8 - Mysqlshivanshu dhawanView Answer on Stackoverflow
Solution 9 - MysqlSalman AView Answer on Stackoverflow
Solution 10 - MysqlAnanttahView Answer on Stackoverflow
Solution 11 - Mysqlabhinay vijayView Answer on Stackoverflow
Solution 12 - MysqlAshwani chaudharyView Answer on Stackoverflow
Solution 13 - MysqlMuraliView Answer on Stackoverflow
Solution 14 - MysqlAshwini MView Answer on Stackoverflow
Solution 15 - MysqlYogenderVarmaView Answer on Stackoverflow
Solution 16 - Mysqlsrishti guptaView Answer on Stackoverflow
Solution 17 - Mysqlmayur nanduView Answer on Stackoverflow
Solution 18 - MysqlMadhur BhaiyaView Answer on Stackoverflow
Solution 19 - MysqlPraveen Kumar CView Answer on Stackoverflow
Solution 20 - Mysqlsarjit07View Answer on Stackoverflow
Solution 21 - MysqlArthur WView Answer on Stackoverflow
Solution 22 - MysqlSmitaView Answer on Stackoverflow
Solution 23 - MysqlShivam ShaktiView Answer on Stackoverflow
Solution 24 - Mysqlishan KankaneView Answer on Stackoverflow
Solution 25 - MysqlgunarevuriView Answer on Stackoverflow
Solution 26 - MysqlChaitanyaView Answer on Stackoverflow
Solution 27 - MysqlAbhi SoniView Answer on Stackoverflow
Solution 28 - MysqlMahbub Ul IslamView Answer on Stackoverflow
Solution 29 - MysqlMahantesh HadimaniView Answer on Stackoverflow
Solution 30 - MysqlAna ClaudiaView Answer on Stackoverflow
Solution 31 - MysqlVinneyView Answer on Stackoverflow
Solution 32 - MysqlSobhit SharmaView Answer on Stackoverflow