1

I am executing the following query in both MSSQL and MySQL

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

It prints all the cities starting with a vowel in MSSQL, but no rows are printed when executed in MySQL (no error)

goutam
  • 657
  • 2
  • 13
  • 35

2 Answers2

2

The only wildcard characters supported by MySQL LIKE are % (for any string) and _ (for any single character). It doesn't support the MSSQL extension [] for character sets. If you need that, use a regular expression, not a wildcard.

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

See List of special characters for SQL LIKE clause for comparisons of LIKE between different SQL implementations.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • [] is not an "MSSQL extension," it is called a CLOSURE. Standard Markovian regular expression syntax (don't confuse with the UNIX shell version of []: rules for "globbing" metacharacters [so called because the expansion used to be done by a separate executable called /etc/glob] are different). Whether MySQL implements that operator is another question. – Bruce David Wilner Jul 14 '16 at 00:00
  • @BruceDavidWilner I know what it is, but it's not part of ANSI SQL patterns. They copied it from regular expressions and added it to their patterns, that's what I mean by it being an extension. – Barmar Jul 14 '16 at 00:02
1

MySQL uses the REGEXP keyword for regular expressions, so you'll want something like 

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]'
Caius
  • 243
  • 1
  • 5