CODEWITHSUNDEEP
sql
Trung
Trung

Reputation: 1382

Wildcard with multiple values?

I get these Challenge on HackerRank

Write a query to print the list of CITY that start with vowels (a, e, i, o, u) in lexicographical order. Do not print duplicates.

My solution:

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%'
Order by City;

Other solution:

select distinct(city) from station
where upper(substr(city, 1,1)) in ('A','E','I','O','U');

This is very clever, but I want to know, if are there any other ways to solve this?

Any kind of DB is OK.

Upvotes: 4

Views: 13605

Answers (3)

Ashwini M
Ashwini M

Reputation: 149

In SQL Server

select distinct city from station 
 where charindex ('a',CITY) =1
    or charindex ('e',CITY) =1
    or charindex ('i',CITY) =1
    or charindex ('o',CITY) =1
    or charindex ('u',CITY) =1

Upvotes: 0

Grzegorz Adam Kowalski
Grzegorz Adam Kowalski

Reputation: 5565

Regular expressions in MySQL / MariaDB:

select distinct city from station where city regexp '^[aeiouAEIOU]' order by city

Upvotes: 4

Luc
Luc

Reputation: 1491

In SQLserver You can use a reg-ex like syntax with like:

select distinct city from station where city like '[aeuio]%' Order by City

Upvotes: 0

Related Questions