Anyone who wants to convert code from MS SQL Server to MySQL will need to find equivalent or similar functions in MySQL. Here is a list of MS SQL Server Functions with their equivalent in MySQL.
There are some functions in MS SQL Server which does not have any equivalent in MySQL, but the same functionality can be acheived by writting SQL Statements.
| MS SQL Server | MySQL | Description | MSSQL to MySQL |
|---|---|---|---|
| ASCII | ASCII() | Returns ASCII value of a Char | Same Functionality in Both MSSQL and MySQL Here is the Example |
| CHAR | CHAR() | Returns the Character of a ASCII value. Reverse of ASCII function | Similar functionalilty with some difference. Example MSSQL and MySQL CHAR Function |
| CHARINDEX | LOCATE(), INSTR() | Use to check whether a String or Char occurs in another string | Same functionality in Both. Click here for Examples |
| CONCAT or + operator | CONCAT() OR CONCAT_WS |
Use to join two or more string together | CONCAT function is introduced in MS SQL Server 2012, in the
previous versions + operator is used to join strings. The equivalent function in MySQL is CONCAT or CONCAT_WS (Concat with Separator) In MySQL + operator is not allowed. Examples Concatenating Strings in MSSQL & MySQL |
| DIFFERENCE | Returns integer value indicating difference between the SOUNDEX values of two strings | No equivalent function in MySQL. For more information Microsoft Doc |
|
| FORMAT | FORMAT, DATE_FORMAT | FORMAT in MS SQL Server is used to format Numbers and Date & Time values | In MySQL FORMAT function can be used to Format Numbers and DATE_FORMAT function has to be used to format Date & Time values. Here is more info |
| LEFT | LEFT | Returns a specified number of characters from left side of a string | Same functionality in both MSSQL and MySQL. Here are more details with examples |
| LEN | LENGTH, CHAR_LENGTH | This function returns the length of a given string. | Similar functionality but there is a difference. Click here for details |
| LOWER | LCASE, LOWER | This function is used to convert any string in any case into lower case. | Same functionality in MSSQL and MySQL. Click here for more |
| LTRIM | LTRIM | LTRIM stands for Left Trim. It returns a string by eliminating any blankspaces occurring on the left side of a string | Same functionality in both MSSQL and MySQL. See the examples of LTRIM in MSSQL and MySQL |
| NCHAR | |||
| PATINDEX | Similar to CHARINDEX but in this function you can also wildcards as pattern | No similar function in MySQL. PATINDEX example can be found here | |
| QUOTENAME | Returns a unicode string with delimiters added | No similar function available in MySQL. Workaround is to create a function manually in MySQL. See Example | |
| REPLACE | REPLACE | Replaces all occurrences of a specified string value with another string value in a given string | Same function is also available in MySQL, but MSSQL REPLACE function is case insensitive whereas in MySQL it is case sensitive. See MSSQL and MySQL Replace Function Examples here |
| REPLICATE | REPEAT | Returns a string by replicating a specified string a specified number of times | Same functionality in MySQL can be acheived by using REPEAT function. Here are examples |
| REVERSE | REVERSE | Returns a string in reverse order | Same functionality in both MSSQL and MySQL. Examples |
| RIGHT | RIGHT | Returns a specified number of characters from right side of a string | Same functionality in both MSSQL and MySQL. Here is the RIGHT function examples in MSSQL and MySQL |
| RTRIM | RTRIM | RTRIM stands for Right Trim. It returns a string by eliminating any trailing blankspaces | Same functionality in both MSSQL and MySQL. See the examples of RTRIM in MSSQL and MySQL |
| SOUNDEX | SOUNDEX | This function is used to check the pronunciation of a word irrespective of how it's written | Same function is available in MySQL with the same name. Click for Examples of SOUNDEX function in both MySQL and MSSQL |
| SPACE | SPACE | Returns a string of specified number of space characters | Same function is also available in MySQL with the same name. Click for more details |
| STR | CAST( N as CHAR) | Converts Number to String | No Exact function is available in MySQL. But using CAST function we can mimic this functionality. Click here for comparison and examples |
| STRING_ESCAPE | Escapes special characters in texts and returns text with escaped characters introduced in SQL Server 2016 | ||
| STRING_SPLIT | Splits the character expression using specified separator. Introduced in SQL Server 2016 | No similar function available in MySQL. But you can write one manually as shown here | |
| STUFF | INSERT | Embeds a string within another string replacing specified number of characters | The same functionality in MySQL is provided by INSERT function. See details |
| SUBSTRING | SUBSTR, SUBSTRING, SUBSTRING_INDEX | Returns a part of a specified String | Same functionality in MySQL is provided by SUBSTRING function. MySQL SUBSTRING function has more features than MSSQL SUBSTRING function. Click here for more |
| UNICODE | Return Unicode Standard integer value of the first character in a specified string | ||
| UPPER | UCASE, UPPER | This function is used to convert any string in any case into lower case. | Same function is available in MySQL also. See Examples |
You can download the MSSQL to MySQL Converter
It converts the MSSQL functions to it's equivalent in MySQL automatically while converting veiws.