CODEWITHSUNDEEP
mysqlsql
Fred
Fred

Reputation: 3

MySQL wildcard query

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!

Upvotes: 0

Views: 441

Answers (2)

user5903421
user5903421

Reputation:

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]$'

Upvotes: 1

Shadow
Shadow

Reputation: 34232

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 %.

Upvotes: 0

Related Questions