CODEWITHSUNDEEP
sqloracle-database
Anish Narayan
Anish Narayan

Reputation: 82

Get city name either do not start with vowels and do not end with vowels

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

FIELD    TYPE
ID       NUMBER
CITY     VARCHAR2(21)
STATE    VARCHAR2(2)
LAT_N    NUMBER
LONG_W   NUMBER

I write the below query but it does not work. Any suggestion?

SELECT DISTINCT (CITY) 
FROM STATION  
WHERE NOT regexp_like(lower(CITY),'^[aeiou].*') 
  AND regexp_like(lower(CITY),'.*[aeiou]$');

Upvotes: 0

Views: 4375

Answers (10)

Lakshmikant Bhusanagi
Lakshmikant Bhusanagi

Reputation: 11

Here is the MySQL query to retrieve the city names that do not start with vowels.

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) NOT IN ('A','E','I','O','U');

Upvotes: 0

UNKNOWN
UNKNOWN

Reputation: 1

SELECT DISTINCT city FROM station WHERE( city NOT LIKE 'A%' AND city NOT LIKE 'E%' AND city NOT LIKE 'I%' AND city NOT LIKE 'O%' AND city NOT LIKE 'U%' ) AND ( city NOT LIKE '%a' AND city NOT LIKE '%e' AND city NOT LIKE '%i' AND city NOT LIKE '%o' AND city NOT LIKE '%u') ;

Upvotes: -1

Patcharapon Chaiporn
Patcharapon Chaiporn

Reputation: 9

MS SQL Servers :

SELECT distinct CITY FROM STATION WHERE CITY NOT LIKE '[AEIOU]%';

Upvotes: 0

LILY
LILY

Reputation: 1

SELECT DISTINCT CITY FROM STATION

WHERE CITY NOT REGEXP '^[AEIOU]' AND CITY NOT REGEXP '[AEIOU]$';

Here is my answer for this challenge on hackerrank and it worked (SQL server) Some of my explanations for this:

*REGEXP Syntax: not case sensitive => both lower and uppercase are included.

*'^[AEIOU]': start with vowels

*'[AEIOU]$': end with vowels

Upvotes: 0

Shritik
Shritik

Reputation: 1

For MS SQL SERVER:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[^aeiouAEIOU]%[^aeiouAEIOU]';

Upvotes: 0

Aravind R
Aravind R

Reputation: 1281

I've got an answer for this!

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT REGEXP '^[aeiou]'
AND CITY NOT REGEXP '[aeiou]$'

Upvotes: 0

user14480205
user14480205

Reputation:

This is My solution in Oracle

SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');

Upvotes: 0

rahul mittal
rahul mittal

Reputation: 1

In Oracle:

SELECT DISTINCT CITY 
FROM STATION 
WHERE REGEXP_LIKE(CITY,'^[^aeiouAEIOU].*[^aeiouAEIOU]$');

Upvotes: -1

mahtabhusain
mahtabhusain

Reputation: 1

try this for ms sql--

select distinct city from station where city not like '[aeiou]%' and city NOT like '%[aeiou]'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You may try this version:

SELECT DISTINCT CITY
FROM STATION
WHERE REGEXP_LIKE(CITY, '^[^aeiouAEIOU].*[^aeiouAEIOU]$');

The regex pattern here matches a whitelist of cities which do not start and do not end with a vowel. Here is an explanation of the regex pattern:

^              from the start of the city name
[^aeiouAEIOU]  match a single non vowel character (lowercase or uppercase)
.*             match any zero or more middle characters
[^aeiouAEIOU]  match another single non vowel character
$              end of the city name

Upvotes: 5

Related Questions