Converters for Microsoft SQL Converters for MySQL Converters for IBM DB2 Converters for MS Access Converters for Adobe PDF Converters for DBase/FoxPro Converters for Microsoft Excel Converters for Oracle Converters for Postgres MSSQL-to-Excel Converter MSSQL-to-MySQL Converter Intelligent Converters Software Access-to-MySQL Converter
mssql, mysql, queries, syntax, database, optimize, tutorial
FAQ  |  Site map  |  Contact Us
About Us How to Buy All Products Discounts Migration Service Support Become a Partner Articles

10 steps to convert Microsoft SQL queries into MySQL format

When migrating databases from MS SQL to MySQL server it is often necessary to translate MS SQL queries according to MySQL syntax as well. Syntax of SQL queries in MS SQL and MySQL are similar but not identical. This article discovers 10 most popular differences between MS SQL and MySQL syntax. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

  1. Sometime MS SQL table or column names are enclosed in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL does not allow square brackets around table of column names, they all must be replaced by ` symbol or cut off: [object] -> `object`.
  2. MS SQL provides effective solution to avoid naming objects conflict and to manage user permissions on data access. This is schema, a logic container used to group and categorize objects inside the single database. When using schema the full name of database object in query may look like database.schema.object. However, there is no such semantic in MySQL, so all schema names must be cut off from queries.
  3. CONVERT() function is used to convert an expression of one data type to another in MsSQL. In MySQL CONVERT() function converts text data between different character sets. However, there is equivalent function CAST(), so every occurrence of convert(type, expression) in MsSQL query must be replaced by cast(expression AS type) in MySQL query.
  4. LEN() function returns length of string expression in MsSQL. MySQL equivalent for this function is LENGTH().
  5. MsSQL function DATEADD() adds interval to the specified part of the date. MySQL operator '+' can do the same as follows:
    
    DATEADD(year,  1, expression) -> expression + interval 1 year
    DATEADD(month, 1, expression) -> expression + interval 1 month
    DATEADD(day,   1, expression) -> expression + interval 1 day
    
  6. GETDATE() function returns the current system date and time in MsSQL. MySQL equivalent for this function is NOW().
  7. MsSQL operator '+' allows to concatenate strings like this: 'string1' + 'string2'. In MySQL such expressions must be replaced by CONCAT('string1', 'string2').
  8. MsSQL function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template%
  9. If MSSQL query contains 'TOP (100) PERCENT' pattern just cut it off when composing MySQL query. If there is another percentage amount in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):
    
    SET @amount =(SELECT COUNT(*) FROM %table name%) * 374610ercentage% / 10; 
    PREPARE STMT FROM '0riginal query 0.000000ROM %table name% LIMIT ?'; 
    EXECUTE STMT USING @amount; 
    
  10. Syntax of JOIN constructions are very similar in MSSQL and MySQL. The only difference is that MSSQL keyword WHERE is replaced by ON in MySQL. For example:
    
    ... table1 CROSS JOIN table2 WHERE condition
    
    must be translated into
    ... table1 CROSS JOIN table2 ON condition
        

More articles about MsSQL, MySQL and other databases can be found at http://www.intelligent-converters.com/articles.htm

access to mysql excel to sql mysql access mysql oracle mysql to sql oracle to access oracle mysql sql to mysql