I have started practising SQL and I think I need to brush up some topics.
This problem statement in hackerrank states that the query lists CITY names from STATION that do not end with vowels.
I tried using wildcard '%[^aeiou]'
SELECT Distinct City
FROM Station
Where City LIKE '%[^aeiou]'
Order By City;
Compiler Message: Answer Wrong.
I know other methods to execute the program but what is wrong with this one. Also, I am wondering how REGEXP '[^aiueo]$'
is working but Like '%[^aeiou]
or Not Like '%[aeiou]'
is not executable?
LIKE
only supports wildcards and you use for very simple match.
REGEXP
or RLIKE
has full regular expression support.
A regular expression is a powerful way of specifying a pattern for a complex search. This section discusses the functions and operators available for regular expression matching and illustrates, with examples, some of the special characters and constructs that can be used for regular expression operations.
See the manual on LIKE and on REGEXP
If you must use LIKE
try this:
SELECT DISTINCT City
FROM Station
WHERE City NOT LIKE '%a'
OR City NOT LIKE '%e'
OR City NOT LIKE '%i'
OR City NOT LIKE '%o'
OR City NOT LIKE '%u';
If you want a faster query use RIGHT (Gordon Linoff answer) or REGEXP