Comparing SQL Server and MySQL Functions

This article is the second part in a series examining some of the challenges in porting your databases between MS SQL Server and MySQL.  In Part 1, we looked at some of the differences between data types utilized by each DBMS.  Today, in moving on to functions, you’ll see how functions may require additional effort on your part to produce equivalent results.

Function Categories

Functions can be divided into three distinct categories:

1. Equivalent Functions: Those that can be safely migrated from one database type to another without any modifications whatsoever.
2. Emulated Functions: Functions that are available in one database, but not the other.  Another problematic issue is that some MySQL functions have a variable parameter count.  In either case, some conversion work is required. 
3. Non-supported Functions: Those which cannot be easily ported because of logical/physical organization and security model differences.

Equivalent Functions

You’ll be happy to know that the following functions are usable in both MySQL and SQL Server queries without any modifications:

ASCII, LEFT, LOWER, LTRIM, REPLACE, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTRING, UPPER, ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, DAY, MONTH, COALESCE, NULLIF, CAST, CONVERT.

Emulated Functions

Functions that have no equivalent on the other platform are where the bulk of your efforts will go, as converting these can be like trying to fit a round peg into a square hole.

The Transact-SQL
CASE function

CASE WHEN @a > @b 
     THEN @a 
     ELSE @b - @a 
END 

This can be converted to the MySQL  IF(expr1, expr2, expr3)  function.  Here’s how it works:

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2; otherwise it returns expr3.

MySQL example:

if(@a>@b, @a, @b-@a) 

Converting Binary Data into a Varchar

IN SQL SERVER 2008 the convert function was extended to support binary data to hex string conversion. Hence, you might see something like this:

CONVERT(NVARCHAR(34), 0xc23eed6b65c93e44a41a2818e274194f, 1) AS BINARY_TO_STRING

The MySQL BIN(N)  function, which returns a string representation of the binary value of N, can be utilized in its stead.

The Transact-SQL  DATALENGTH Function

This one is easy to convert because both the SQL Server DATALENGTH and MySQL BIT_LENGTH functions will return the length of a string in bits.

String Concatenation

SQL Server does not support the ANSI SQL CONCAT() function.  Instead, it uses the plus operator (+) for string concatenation:

'A'+'B'+'C', 'A'+'#'+'B'+'#'+'C' 

In MySQL, use the CONCAT(str1, str2, ….) or CONCAT_WS(separator, str1, str2, ...)  functions, which return the concatenated arguments:

CONCAT('A','B','C'), CONCAT_WS('#','A','B','C') 

Converting Numbers between Different Number Bases

It is sometimes useful to convert a number to a non-base 10 string. In SQL Server, that requires using the CAST function or employing a user-defined function.  In MySQL, you can forget about all that, as the ANSI SQL CONV(N, from_base, to_base)  function will allow you to convert from one base to another with ease.

Finding the Position of the First Occurrence of a Substring within a String

The Transact-SQL CHARINDEX function maps exactly to the ANSI  SQL LOCATE() function.

Inserting a String within Another

In SQL Server, the REPLACE function can be used to replace part of a string with another. For instance, the following example replaces the string def in abcdefghi with xyz.

SELECT REPLACE('abcdefghicde','def','xyz');

MySQL’s INSERT(str, pos, len, newstr) function is a reasonable facsimile, as it returns the string str, with the substring that begins at position pos and is len characters long replaced by the string newstr.

Loading Data and Statements from a File

T-SQL bulk load statements and extended stored procedures that load data and executable statements from a text file can be replace with LOAD_FILE(file_name)  in MySQL.

Getting the Current Date

Transact-SQL’s  NOW function maps to GETDATE in ANSI SQL.

Generating a Repeating String

Transact-SQL’s  REPLICATE function maps exactly to REPEAT in ANSI SQL.

Testing for NULL

Transact-SQL relies on the CASE and IS NULL clauses to check for NULL values. In MySQL, you can simply use the ISNULL(expr) function instead.  If expr is NULL, ISNULL() returns 1; otherwise it returns 0.

Comparing Two Strings

Transact-SQL relies on comparison operators to compare strings, whereas ANSI SQL provides the STRCMP(expr1, expr2)  function.

Formatting Dates

While Transact-SQL uses a combination of date, string, and convert functions to format dates as strings, ANSI SQL has the built-in DATE_FORMAT(date, format) function specifically for formatting dates.

Adding an Interval to a Given Date

The Transact-SQL DATEADD function does have equivalents in Oracle, DB2, and PostgreSQL. MySQL includes the same function, except that it’s called DATE_ADD:

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);

returns '2011-01-01 23:59:59'

Converting between Seconds and a Time

In Transact-SQL, converting between seconds and a time such as 12:34:00 can be accomplished using a combination of the CONVERT and DATEADD functions.  For instance, here is a statement that converts seconds to a time:

CONVERT(char(8), DATEADD(second, Diff, '0:00:00'), 108)

MySQL can convert between seconds and a time more easily using the SEC_TO_TIME(seconds)  and TIME_TO_SEC(time) functions.

Retrieving the Last Inserted ID

The Transact-SQL @@IDENTITY and SCOPE_IDENTITY functions are used to retrieve the last inserted ID.  MySQL possesses a similar function called LAST_INSERT_ID for this purpose.

Concatenating Column Values

To concatenate the contents of a column into a string requires a few steps in T-SQL:

declare @v varchar(max) 
set @v='' 

select @v=@v+','+isnull(field_a,'') from table_1 
select substring(@v,2,len(@v)) 

It’s much easier in ANSI SQL, thanks to the GROUP_CONCAT function.  It comes in two flavors to support 
different formats:

• GROUP_CONCAT( Language SEPARATOR ‘-’ ) will use the dash instead of  the default comma separator.

• SELECT GROUP_CONCAT( Language ORDER BY Language DESC ) can be used to change the sorting order.

Note that GROUP_CONCAT ignores NULL values.

Non-supported Functions

Any SQL Server-centric functions have to be either removed and/or rewritten using a combination of ANSI SQL statements.  Once completed, the new code can be saved as a user-defined function for easy reuse.

Conversion Tools

There are purportedly some automated tools that can convert stored procedures between SQL Server and MySQL, such as SQLWays by Ispirer.   According to their site and anecdotal reports, it converts stored procedures, functions, packages and triggers.  All this automation doesn’t come cheap; at about a grand USD, it may be more cost effective to manually convert your procs.

출처 : http://www.databasejournal.com/features/mysql/comparing-sql-server-and-mysql-functions.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DatabaseJournalNews+%28Database+Journal+News%29&utm_content=Google+Feedfetcher