LIKE Function in SQL Server

Kaustubh Dwivedi 0
Viewed 66

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

https://www.hackerrank.com/challenges/weather-observation-station-12/problem

Using the LIKE operator I came up with 3 queries as follows :

Working

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT LIKE '[aeiou]%' 
  AND CITY NOT LIKE '%[aeiou]'

Working

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

Not working:

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY NOT LIKE '[aeiou]%[aeiou]';

Can someone tell me why this third query is not working?

sql-like select sql-server sql
3 Answers

The third approach finds cities that don't start and end with a vowel. It will incorrectly return cities that start with a vowel but don't end with one, or cities that don't start with a vowel but do end with one.

Mureinik0 
WHERE CITY NOT LIKE '[aeiou]%' 
  AND CITY NOT LIKE '%[aeiou]'

This query returns rows which do not start with a character that is a vowel and do not end with it.

This is essentially exactly the question as asked.

WHERE CITY LIKE '[^aeiou]%[^aeiou]';

This query returns rows which do: start with a character that is not a vowel and also end with a character that is not a vowel.

This is not quite the same, as it requires at least two characters, whereas the first version only requires a single one.

WHERE CITY NOT LIKE '[aeiou]%[aeiou]';

This query returns rows which do not: start with a character that is a vowel and also end with a character that is a vowel.

This is not at all the same as the previous queries. Another way of expressing it is: rows which do start with a non-vowel or end with it.

Charlieface0

All your answers are incorrect! This is it:

select DISTINCT CITY from STATION where CITY LIKE '[^aeiouAEIOU]%[^aeiouAEIOU]';

nikhil sugandh0
Related