MySQL VS MsSQL Syntax | Code Factory

Reference Link : Link

Donate : Link

Table : MENU_MASTER

CREATE TABLE menu_master (
    id int(11) NOT NULL,
    menu_name varchar(45) NOT NULL,
    menu_action varchar(45) NOT NULL,
    parent_id int(11) NOT NULL,
    menu_order int(11) DEFAULT NULL,
    PRIMARY KEY (id)
)

MySQL

FIND_IN_SET : The FIND_IN_SET() function returns the position of a string within a list of strings.

Syntax : FIND_IN_SET(string, string_list)

SELECT * FROM MENU_MASTER WHERE FIND_IN_SET(ID, '1,2');

MsSQL | SQL Server

STRING_SPLIT : A table-valued function that splits a string into rows of substrings, based on a specified separator character.

Syntax : STRING_SPLIT (string , separator)

SELECT * FROM MENU_MASTER WHERE ID IN (SELECT * FROM STRING_SPLIT ('1,2', ','));

CONCAT : The CONCAT() function adds two or more strings together.

Syntax : CONCAT(string1, string2, ...., string_n)

SELECT * FROM MENU_MASTER WHERE CONCAT(',','1,2',',') LIKE CONCAT('%,',ID,',%');

MySQL

SUBSTRING_INDEX : The SUBSTRING_INDEX() function returns a substring of a string before a specified number of delimiter occurs.

Syntax : SUBSTRING_INDEX(string, delimiter, number)

SELECT SUBSTRING_INDEX("www.test.com", ".", 1);

MsSQL | SQL Server

CHARINDEX : The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0.

Syntax : CHARINDEX(substring, string, start)

SUBSTRING : The SUBSTRING() function extracts some characters from a string.

Syntax : SUBSTRING(string, start, length)

CHARINDEX + SUBSTRING

select 
     CASE 
          WHEN CHARINDEX('.', 'www.test.com') > 1 THEN
               SUBSTRING('www.test.com', 1, CHARINDEX('.','www.test.com')-1) 
          ELSE 'www.test.com'
     END AS SUBSTRING;

MySQL

GROUP_CONCAT : MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.

Syntax : GROUP_CONCAT(expr)

SELECT PARENT_ID, GROUP_CONCAT(ID SEPARATOR ' / ') AS ids FROM MENU_MASTER GROUP BY PARENT_ID;

ISSUE : Duplicate data

SELECT GROUP_CONCAT(MENU_ORDER SEPARATOR ' / ') AS IDS FROM MENU_MASTER;

Solution

SELECT GROUP_CONCAT(DISTINCT MENU_ORDER SEPARATOR ' / ') AS IDS FROM MENU_MASTER;

MsSQL | SQL Server

STRING_AGG : Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

Syntax : STRING_AGG (expression, separator)

SELECT PARENT_ID, STRING_AGG(ID, ' / ') AS ids FROM MENU_MASTER GROUP BY PARENT_ID;

ISSUE : Duplicate data

SELECT STRING_AGG(MENU_ORDER, ' / ') AS IDS FROM MENU_MASTER;

Solution

SELECT STRING_AGG(MM.MENU_ORDER, ' / ') AS IDS FROM (SELECT DISTINCT MENU_ORDER FROM MENU_MASTER) MM;

MySQL

MOD : The MOD() function returns the remainder of a number divided by another number.

Syntax : MOD(x, y) OR x MOD y OR x % y

1. SELECT MOD(10, 4) AS MODULO;
2. SELECT (10 MOD 4) AS MODULO;
3. SELECT (10 % 4) AS MODULO;

MsSQL | SQL Server

% : Returns the remainder of one number divided by another.

Syntax : dividend % divisor

SELECT (10 % 4) AS MODULO;

Note : 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

MySQL

DAYOFWEEK : The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).

Syntax : DAYOFWEEK(date)

1. SELECT DAYOFWEEK("2019-07-25") AS DAYOFWEEK;
2. SELECT DAYOFWEEK("2019-07-25 11:44:22") AS DAYOFWEEK;
3. SELECT DAYOFWEEK(CURDATE()) AS DAYOFWEEK;

MsSQL | SQL Server

DATEPART : The DATEPART() function returns a specified part of a date. This function returns the result as an integer value.

Syntax : DATEPART(interval, date)

1. SELECT DATEPART(DW, '2019-07-25') AS DATEPART;
2. SELECT DATEPART(WEEKDAY, '2019-07-25') AS DATEPART;
3. SELECT DATEPART(W, '2019-07-25') AS DATEPART;

Required. The part to return. Can be one of the following values:
year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear = Day of the year
day, dy, y = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second millisecond, ms = Millisecond

Get DATE From YEAR And WEEK NUMBER (Ex. 2019-20)

declare @yr_wk varchar(10) set @yr_wk = '2019-20'
SELECT REPLACE(CONVERT(varchar, DATEADD(week, SUBSTRING(@yr_wk, 6, 2) - 1, CONCAT(SUBSTRING(@yr_wk, 1, 4),'-01-01')) - (DATEPART(weekday, CONCAT(SUBSTRING(@yr_wk, 1, 4),'-01-01')) - 1), 102), '.', '-') AS DATE_FROM_YEAR_WEEK_NUMBER;

In (DATEPART(weekday, CONCAT(SUBSTRING(@yr_wk, 1, 4),'-01-01')) - 1)
-1 is for Sunday
For Monday use - 2 and so on.

MySQL

IF : The IF() function returns a value if a condition is TRUE, or another value if a condition is FALSE.

Syntax : IF(condition, value_if_true, value_if_false)

SELECT IF(500 < 1000, "YES", "NO") AS IF_CONDITION;

MsSQL | SQL Server

CASE WHEN : Evaluates a list of conditions and returns one of multiple possible result expressions.

Syntax :
CASE
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

SELECT CASE WHEN (500 < 1000) THEN 'YES' ELSE 'NO' END AS IF_CONDITION;