MySQL wildcard query

Question

I'm relatively new with SQL and I ran into a problem/question while doing some practice problems.

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

Here's the query I used with MySQL:

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

I'm confused why this doesn't work. Here's my thinking for the query:

SELECT DISTINCT CITY

^ make sure each city returned isn't repeated

FROM STATION

^ from the STATION table

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

^ CITY names that are selected can have the first letter begin with [aeiou], have anything in between, and end with [aeiou].

Any help or advice would be appreciated, thanks!

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

If you are using regex, you can use regexp or RLIKE in place of LIKE. The other thing you need to do is put ^ to denote the first character, $ to denote the last character, and .* for wildcard. See this and this:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY RLIKE '^[aeiou].*[aeiou]$'

edited May 23 '17 at 11:59

Community

1
1

answered Feb 09 '16 at 22:51

score 0 · Answer 2 · answered Feb 09 '16 at 22:49

The like operator has only 2 wildcard characters: % and _, it does not handle the bracket syntax. You need regular expressions for that, and you can use the rlike operator for that. But in regular expressions you need to use . instead of _ and .* instead of %.

MySQL wildcard query

2 Answers2