I don't really like this article, as a lot of the tips are lacking background information or giving less than optimal advise.
Note that I have no hands-on experience with MySQL; the below comments are based on what I found on internet.
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`.
The square brackets used by SQL Server are a hideous deviation from the ANSI standard. And there is no reason why everyone keeps using them, as the ANSI standard delimiter, double quotes, is supported by SQL Server as well. So unless you are really sure that you'll never port to other databases, avoid the [square brackets] and uses "quotation marks" instead. (Or, better yet, stick to identifier names that don't need delimiters).
MySQL also supports ANSI-standard delimiters - but only if it is running in ANSI mode. Otherwise, you will have to convert your code to use the non-standard back ticks (but why would you not run in ANSI mode when you are developing a project that needs to be as compatible as possible across products?)
3) CONVERT() function is used to convert an expression of one data type to another in Microsoft SQL. 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 MS SQL query must be replaced by 'cast(%expression% AS %type%)' in MySQL query.
Because CAST is ANSI-standard and CONVERT is not, I prefer to use CAST anyway. However, CONVERT gives more control, for instance when converting date values to string values in a specific format. For MySQL, you'd have too convert to DATE_FORMAT to get the same results.
4) LEN() function returns length of string expression in MS SQL. MySQL equivalent for this function is LENGTH().
Unless I misunderstand the MySQL documentation, their LENGTH() is actually equivalent to SQL Servers DATALENGTH() (number of bytes in storage, so twice the number of characters for Unicode). SQL Server's LEN() returns the number of characters; MySQL uses CHAR_LENGTH() for that.
6) GETDATE() function returns the current system date and time in MS SQL. MySQL equivalent for this function is NOW().
Or use ANSI standard CURRENT_TIMESTAMP, which works on both databases.
8) MS SQL function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template%
I'd say that the MySQL expression LIKE '%template%' is actually equivalent to the MS SQL Server expression LIKE '%template%' - yes, those are equal, and they are ANSI.
The CONTAINS function only works when full-text search is used and set up (which takes some effort, and introduces some overhead - so use with care!), and it allows for much more than the simple pattern matching of LIKE. I have not tried to find out if MySQL offers anything to replace SQL Server's Full-Text Search capabilities.
9) If MS SQL 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):
The first part of that sentence can be shortened to "If MS SQL query contains 'TOP (100) PERCENT' pattern just cut it off". It's just a waste of bytes.
The replacement pattern looks quite inefficient to me (as the query has to be executed twice), but since I never used MySQL, I can't verify it. I'd also be concerned about the risk of SQL injection, since it uses dynamic SQL.
10) Syntax of JOIN constructions are very similar in MS SQL and MySQL. The only difference is that MS SQL keyword 'WHERE' is replaced by 'ON' in MySQL. For example:
While the example given will probably work, this recommendation as a whole is pure nonsense. For most join types (inner, left outer, right outer, full outer), the syntax is exactly the same. Both SQL Server and MySQL require an ON clause to be present, and both SQL Server and MySQL optionally allow a WHERE clause as well. In a cross join (as given in the example), there is indeed a slight difference - SQL Server does not allow an ON clause and does allow a WHERE clause; MySQL also allows a WHERE clause, but optionally allows an ON clause as well. Since this is completely non-ANSI, I recommend against using this non-standard option and sticking to the WHERE clause.
It then became even more interesting when I followed the link - and found a website from a vendor that sells conversion tools, but also includes some articles. Including an exact (word for word) copy of this article.
1. I'll never buy a product from "Intelligent Converters", nor advise a client to do so - given the amount of bad advice given in their article, I don't think I'll like the results of their automated conversion tools.
2. Unless the author of this SQL Server Central article works for "Intelligent Converters", submitting this article is a flagrant breach of copyright.