CODEWITHSUNDEEP
mysql
Hemant Raghuwanshi
Hemant Raghuwanshi

Reputation: 71

How to retrieve rows that begin and end with vowels?

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.

enter image description here

Upvotes: 1

Views: 1077

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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')

Upvotes: 0

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38552

You can try using REGEXP

Explanation: The below query will select all the DISTINCT rows where cities starts with a vowel as first characters, followed by any other characters zero or more times and finally also contains a vowel characters at end.

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

Upvotes: 1

Related Questions