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(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;
. . . . .
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';
Code Factory 