CODEWITHSUNDEEP
sqlsql-server
Lax Mandis
Lax Mandis

Reputation: 131

Not beginning with vowels and not ending with vowels

I'm practicing a challenge on Hackerrank where the question is return list of all cities from a Table 'STATION' where the cities do not begin with vowels and do not end with vowels.

This query doesn't work:

select distinct CITY from STATION where CITY NOT LIKE '[A,E,I,O,U]% 
[a,e,i,o,u]'

But this query does work:

select distinct CITY from STATION where CITY NOT LIKE '[A,E,I,O,U]%' 
AND CITY NOT LIKE '%[a,e,i,o,u]'

Could anyone please help me understand why the first query is not working?

Upvotes: 0

Views: 7507

Answers (9)

Budige Avinash
Budige Avinash

Reputation: 1

Use basic LIKE statements to select the first letter. Here, I have used NOT LIKE to exclude vowels.

SELECT DISTINCT city
FROM station
WHERE LOWER(city) NOT LIKE 'a%'
AND LOWER(city) NOT LIKE 'e%'
AND LOWER(city) NOT LIKE 'i%'
AND LOWER(city) NOT LIKE 'o%'
AND LOWER(city) NOT LIKE 'u%';

Upvotes: 0

Deepak
Deepak

Reputation: 490

give a try on below code

select distinct city from station
where left(city,1) not in ('a','e','i','o','u')
and right(city,1) not in ('a','e','i','o','u')

Upvotes: 0

Er Ankit Sukhija
Er Ankit Sukhija

Reputation: 1

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

Upvotes: 0

Sez
Sez

Reputation: 1

Here is the solution:

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: 0

Akash Verma
Akash Verma

Reputation: 847

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%' ORDER BY City

Upvotes: 0

Anusuya Roy
Anusuya Roy

Reputation: 29

Try with MySQL solution :

select distinct CITY from STATION where substr(CITY, 1, 1) not in ('a','e','i','o','u') and substr(CITY, -1, 1) not in ('a','e','i','o','u');

Upvotes: 0

ebuzz168
ebuzz168

Reputation: 1194

select distinct city
from station
where city rlike '^[^aeiouAEIOU].*' and city rlike '^.*[^aeiouAEIOU]$'

Upvotes: 4

Frank Ball
Frank Ball

Reputation: 1126

Try this:

    SELECT DISTINCT CITY  FROM STATION  WHERE UPPER(SUBSTR(CITY, 1, 1)) NOT IN ('A', 'E', 'I', 'O', 'U')
     AND UPPER(SUBSTR(C.CITY, LENGTH(CITY), 1)) NOT IN ('A', 'E', 'I', 'O', 'U')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your second query sort of works -- because commas are not going to appear at the beginning or end of the name.

In all likelihood. the desired solution is a single pattern:

where city like '[^aeiou]%[^aeiou]'

Upvotes: 4

Related Questions