Introduction

Hello everyone! In this blog post, I want to share my experience with a SQL exercise I encountered on HackerRank. The task was to retrieve cities from the table “Station” that start and end with vowels. I’ll explain my approach to solving this problem using a concise SQL query.

The Problem

During my SQL practice on HackerRank, I stumbled upon an exercise that required me to extract specific cities from the “Station” table. The condition was that the cities should start and end with vowels.

Discovering the String Operators

In SQL, the ‘LIKE’ operator is commonly used to search for patterns. While attempting to solve the problem, I wanted to explore more string operators to find an efficient solution. That’s when I decided to do some online research, and to my delight ‘voila’, I came across the ‘REGEXP’ operator. This operator opened up new possibilities for me to solve the problem more effectively.

The Solution

To tackle this exercise without writing a long SQL query, I decided to use the ‘REGEXP’ operator. Here’s the concise query I used to solve the problem:

 

 

Explanation

Let me break down the query for you. The ‘SELECT DISTINCT’ statement retrieves the unique city names from the ‘station’ table. The ‘WHERE’ clause specifies the condition we want to apply, using the ‘REGEXP’ operator. The regular expression pattern ‘^[aeiouAEIOU].*[aeiouAEIOU]$’ ensures that the city names start and end with vowels.

Conclusion

In this blog post, I shared my experience with a SQL exercise on HackerRank, where I had to retrieve cities starting and ending with vowels from the ‘Station’ table. I discovered the ‘REGEXP’ operator, which allowed me to solve the problem in a concise and efficient manner. By using the SQL query mentioned above, I was able to extract the desired cities effortlessly.

Note: need to have some background in regex in order to know some regex operators. check out: https://regexone.com

I hope you found this blog post helpful in understanding how to solve similar SQL problems efficiently. Happy coding!

The Data School
Author: The Data School