Query data starting and ending with vowels using SQL LOWER(), LENGTH() and SUBSTRING() functions

Author: Al-mamun Sarkar Date: 2020-03-28 09:32:55

Write a SQL query to get all cities starting and ending with vowels from the STATION table using LOWER(), LENGTH() and SUBSTRING() functions. This is the solution of HackerRank's Weather Observation Station 8 SQL problem.

Table name: STATION

Table Structure:

Fileds Type
ID NUMBER
CITY VARCHAR
STATE VARCHAR
LAT_N NUMBER
LONG_W NUMBER

 

SQL Query:

SELECT CITY FROM STATION
WHERE LOWER(SUBSTRING(CITY, LENGTH(CITY), 1)) IN ('a', 'e', 'i', 'o', 'u')
AND LOWER(SUBSTRING(CITY, 1, 1)) IN ('a', 'e', 'i', 'o', 'u')