MySQL VS MsSQL Syntax | Code Factory


Donate : Link

Medium Blog : Link

Applications : 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(stringstring_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;

. . . . .

Show Table Schema

MySQL

Syntax :

DESC TABLE_NAME;
OR
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLE_NAME';

MsSQL | SQL Server

Syntax :

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLE_NAME';

. . . . .

Get All Table Names

MySQL

Syntax :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'

MsSQL | SQL Server

Syntax :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

. . . . .

Last day of the month for the given date

MySQL

Syntax : LAST_DAY(date)

LAST_DAY('2019-09-09')

MsSQL | SQL Server

Syntax : EOMONTH(start_date [, offset] );

SELECT EOMONTH('2019-02-15');
SELECT EOMONTH('2019-02-15', 2);

. . . . .

Get 1st day of prev month from given date

MySQL

(SELECT DATE_ADD((SELECT DATE_ADD(DATE_ADD(LAST_DAY('2019-09-09'), INTERVAL 1 DAY), INTERVAL - 1 MONTH)), INTERVAL - 1 MONTH));

MsSQL | SQL Server

SELECT DATEADD(DAY,1,EOMONTH('2019-09-09',-2));

. . . . .

Get Previous Week’s Sunday, Monday, Tuesday … from the Givern Date

MySQL

SELECT DATE_SUB(date('2020-03-28'), INTERVAL 
	(case when WEEKDAY('2020-03-28')=0 then 7 
	when WEEKDAY('2020-03-28')=6 then 6
	when WEEKDAY('2020-03-28')>0 then 7+WEEKDAY('2020-03-28') end)
	day) as 'Prev Monday';
SELECT DATE_SUB(date('2020-03-22'), INTERVAL 
	(case when WEEKDAY('2020-03-22')=1 then 7 
	when WEEKDAY('2020-03-22')=6 then 5
	when WEEKDAY('2020-03-22')>1 then 6+WEEKDAY('2020-03-22') 
	else 6 end)
	day) as 'Prev Tuesday';
SELECT DATE_SUB(date('2020-03-29'), INTERVAL 
	(case when WEEKDAY('2020-03-29')=2 then 7 
	when WEEKDAY('2020-03-29')=6 then 4
	when WEEKDAY('2020-03-29')>2 or WEEKDAY('2020-03-29')<2 then 5+WEEKDAY('2020-03-29') 
	end)
	day) as 'Prev Wednesday';
SELECT DATE_SUB(date('2020-03-29'), INTERVAL 
	(case when WEEKDAY('2020-03-29')=3 then 7 
	when WEEKDAY('2020-03-29')=6 then 3
	when WEEKDAY('2020-03-29')>3 or WEEKDAY('2020-03-29')<3 then 4+WEEKDAY('2020-03-29') 
	end)
	day) as 'Prev Thursday';
SELECT DATE_SUB(date('2020-03-28'), INTERVAL 
	(case when WEEKDAY('2020-03-28')=4 then 7 
	when WEEKDAY('2020-03-28')=5 then 8
	when WEEKDAY('2020-03-28')<4 then 3+WEEKDAY('2020-03-28') 
	else 2 end)
	day) as 'Prev Friday';
SELECT DATE_SUB(date('2020-03-28'), INTERVAL 
	(case when WEEKDAY('2020-03-28')=5 then 7 
	when WEEKDAY('2020-03-28')=6 then 1
	when WEEKDAY('2020-03-28')>=0 then 2+WEEKDAY('2020-03-28') end)
	day) as 'Prev Saturday';
SELECT DATE_SUB(date('2020-03-28'), INTERVAL 
	(case when WEEKDAY('2020-03-28')=6 then 7 
	else 8+WEEKDAY('2020-03-28') end)
	day) as 'Prev Sunday';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s