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

Question

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]$');

Answer 1

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

Answer 2

try this for ms sql--

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

Answer 3

This is My solution in Oracle

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

Answer 4

I've got an answer for this!

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

Answer 5

For MS SQL SERVER:

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

Answer 6

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

Answer 7

In Oracle:

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