How-ToMySQLSQL

How To Convert MS SQL Queries to MySQL

2 Mins read
How To Convert MS SQL Queries to MySQL

When you are migrating from the Microsoft SQL to MySQL server, the SQL queries are to be converted to MySQL syntax. Even if the SQL queries and the MySQL syntax are similar in nature, they are not completely identical. The differences between them can be identified through the Oracle ERP database management system. Now, let us go through all the differences.

Square Brackets

This type of bracket features are used for the column or the table names in Microsoft SQL. The queries are included within the square brackets when there are spaces. On the other hand, MySQL will not allow square brackets for the column or the table names. They are simply used with symbols or cut offs. For example [object] -> ‘object’

Schema Names 

The conflict of the naming objects can be avoided with the help of MS SQL. The user permissions on the data access can also be managed with MS SQL. Here, the schema can be used as the logic container for differentiating the groups and the categories of the single database structure. The query using the schema can be like this %database%.%schema%.%object%. In MySQL syntax, there is no semantic query present. Therefore, the schema names can be separated from the queries.

Convert Function 

In MS SQL, the CONVERT() function can be used for translating one type of data into another type. The text data can be converted to the different sets of characters through the CONVERT () function in MySQL. A similar function CAST() is used in the queries of MySQL. Therefore all the CONVERT function of the MS SQL (%type%.%expression%) can be translated to the CAST function ( %expression% AS %type%) of the MySQL.

LEN () Function 

In MS SQL, the length string expression can be measured with the LEN function. In case of MySQL, the LENGTH () function can be used in this case.

DATEADD Function 

This function is generally used for including intervals in some specific parts of the date. The + function in MySQL can perform the similar operation like this:

DATEADD (year, 2 , %expression% ) -> %expression% + interval 2 years
DATEADD (month, 2 %expression %) -> %expression% + interval 2 months
DATEADD (day, 1, %expression%) -> %expression% + interval 2 days

GETDATE Function 

This function shows the current date of the system through MS SQL. In case of MySQL, the function is just similar to the NOW () function.

‘+’ Function

Different strings can be added in the MS SQL operator with the + function. For example, ‘string 1’ + ‘string 2’. In case of MySQL, you can use the CONCAT function (string 1, string 2)

‘CONTAINS’ Function 

This particular function of MS SQL checks whether there are any matches inside the expression with different templates. The search function can take place through ( expression, template) . In case of MYSQL the same semantics can be implemented with LIKE ( %template%)

Top (100) PERCENT 

In MS SQL this pattern can be used to obtain the highest percentages of the queries. In MySQL, the percentages can be replaced by the following codes:

SET @ amount = ( SELECT COUNT (*) FROM ( %tablename%) * %percentage% / 10
PREPARE STMT FROM %originalquery% FROM %tablename% LIMIT
EXECUTE STMT USING @amount

JOIN Constructions 

The function of the JOIN is almost similar in both MS SQL and MYSQL. The only difference is that the WHERE keyword in MS SQL should be replaced with NOW in MySQL.

Therefore, being similar database management systems, the queries and the syntaxes of MS SQL and MySQL are slightly different. Now, as you know the process of converting the queries from one another, you can use any of them without any hindrance.

Lalit Sharma is an SEO consultant who runs a SEO house called Ranking By SEO. He is specialized in link building and other SEO related activities. You can also find him on Twitter, Google+ and his personal site.

Leave a Reply

Your email address will not be published. Required fields are marked *