goutam goutam - 4 months ago 17
MySQL Question

SQL expression using regex difference between MSSQL and MySQL

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)

Answer

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.

Comments