CODEWITHSUNDEEP
mysqlsql
nsledeski
nsledeski

Reputation: 37

HackerRank: LIKE vs REGEXP

Working through Weather Observation Station 6 on hackerRank, and I can't understand why the former code works but the latter does not. Doesn't my like clause detect a first letter starting with a vowel and then any wildcards following it? Just as the regexp anchors at the beginning of the string and checks that the first character is a vowel. Why aren't they equivalent?..

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

vs. 

SELECT DISTINCT city FROM station WHERE city REGEXP '^[aeiou]';

Upvotes: 2

Views: 1444

Answers (3)

Leo
Leo

Reputation: 87

I have re-written your regex query this one will work.

as you missed '|' symbol the regex was searching for the exact match "aeiou"

SELECT DISTINCT city FROM station WHERE city REGEXP '^[a|e|i|o|u]';

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

like doesn't work that way. it looks for a string starting with [aeiou] per your query.

To get the values starting with a vowel using like, you should do

SELECT DISTINCT CITY FROM STATION 
WHERE CITY LIKE 'a%' or city like 'e%' or city like 'i%' or city like 'o%'
or city like 'u%'

Edit:

As mentioned in the comments by @dnoeth and @Martin Smith, like '[aeiou]%' would work in SQL Server (T-SQL extension). However, regexp isn't supported in SQL Server. In all other databases from what i know, like '[aeiou]%' would match the literal string starting with '[aeiou]'.

Upvotes: 2

Dylan Su
Dylan Su

Reputation: 6065

like and regexp are quite different.

like only recognizes asterisk '%' and '_', while regexp use regular expression, which is more powerful.

Here is a demo:

mysql> select 'ixxx' LIKE '[aeiou]', 'ixxx' REGEXP '[aeiou]';
+-----------------------+-------------------------+
| 'ixxx' LIKE '[aeiou]' | 'ixxx' REGEXP '[aeiou]' |
+-----------------------+-------------------------+
|                     0 |                       1 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

Upvotes: 0

Related Questions