split FullName column to FirstName, MiddleName, LastName column in MS SQL

DECLARE @fullname VARCHAR(60) = 'Ram Gopal Varma'
       ,@FirstName VARCHAR(20)
       ,@MiddleName VARCHAR(20)
       ,@LastName VARCHAR(20)
       ,@1stSpaceIndex INT
       ,@2ndSpaceIndex INT
       ,@3rdSpaceIndex INT
 
SET @1stSpaceIndex = CHARINDEX(' ', @fullname) -- Get 1st Space Index
SET @2ndSpaceIndex = CHARINDEX(' ', @fullname, @1stSpaceIndex + 1) --Get 2nd Space Index with start location
SET @3rdSpaceIndex = CHARINDEX(' ', REVERSE(@fullname)) -- Get 3rd Space Index using reverse fuction
 
--Get 1st name using left function
SET @FirstName = LEFT(@fullname, @1stSpaceIndex - 1) --(-1 remove the space count)
 
--Check 2nd name exists or not
IF @2ndSpaceIndex <> 0
BEGIN
       --Get 2nd name using substring function
       SET @MiddleName = SUBSTRING(@fullname, @1stSpaceIndex + 1, @2ndSpaceIndex - @1stSpaceIndex - 1)
END
 
--Get 3rd name using left & reverse function
SET @LastName = REVERSE(LEFT(REVERSE(@fullname), @3rdSpaceIndex))
 
SELECT @1stSpaceIndex AS '1stSpaceIndex'
       ,@2ndSpaceIndex AS '2ndSpaceIndex'
       ,@3rdSpaceIndex AS '3rdSpaceIndex'
 
SELECT @FirstName AS 'FirstName'
       ,@MiddleName AS 'MiddleName' 

       ,@LastName AS 'LastName' 

Results

1stSpaceIndex 2ndSpaceIndex 3rdSpaceIndex
------------- ------------- -------------
4             10            6
 
(1 row affected)
 
FirstName            MiddleName           LastName
-------------------- -------------------- --------------------
Ram                  Gopal                 Varma

(1 row affected)

Select odd and even rows in the SQL

CREATE TABLE #TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
 
GO
 
INSERT INTO #TEST (ROWID) VALUES (NEWID())
 
GO 10
 
--drop table #TEST
 
select * from #TEST where Id % 2 = 0 --Even

select * from #TEST where Id % 2 != 0 --Odd

User Defined Functions in SQL

 CREATE FUNCTION ScalarValueFunction()
RETURNS VARCHAR(50)
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      RETURN @date
END
 
SELECT dbo.ScalarValueFunction() AS CurrentDate
/*
      1. It returns only one parameter value.
      2. It supports complex logic & in the end, returns only one parameter value.
      3. Cannot access temporary tables from within a function.
      4. It starts and ends with BEGIN...END block.
      5. It is directly used in the SELECT statement.
*/
 
 
CREATE FUNCTION InlineTableValueFunction()
RETURNS TABLE
AS
RETURN
(
      --DECLARE @date DATETIME --Throw an error
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --SET @date = GETDATE()
 
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
      UNION
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
)
 
SELECT * FROM dbo.InlineTableValueFunction()
/*
      1. It returns the table type parameter value.
      2. It does not allow DECLARE & CREATE keyword.
      3. Cannot access temporary tables from within a function.
      4. It starts with the RETURN block.
      5. It allows only one SELECT statement result.
      6. It is used in the FROM statement.
      7. It is also called the inline table-valued function.
*/
 
CREATE FUNCTION MultiStatementTableValueFunction()
RETURNS @DateTable TABLE(CurrentDate VARCHAR(50), TomorrowDate VARCHAR(50))
AS
BEGIN
      DECLARE @date DATETIME
 
      --CREATE TABLE #DateTable([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      --DECLARE @DateTable1 AS TABLE([Date] DATETIME) --Throw an error
      --INSERT INTO #DateTable1 VALUES(GETDATE()) --Throw an error
      --SET @date = (SELECT [Date] FROM #DateTable) --Throw an error
 
      SET @date = GETDATE()
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      INSERT INTO @DateTable
      SELECT GETDATE() AS CurrentDate,GETDATE()+1 AS TomorrowDate
 
      RETURN
END
 
SELECT * FROM dbo.MultiStatementTableValueFunction()
/*
      1. It returns the table structure.
      2. Cannot access temporary tables from within a function.
      3. It starts and ends with BEGIN...END block.
      4. It is used in the FROM statement.
      5. It must have a RETURN keyword.
      6. The function body can have one or more than one statement.
      7. It is also called the table-valued function.

*/

Running total in SQL

CREATE TABLE #Employee(Id INT IDENTITY,Name VARCHAR(100), Salary money)
 
INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

SELECT *, SUM(Salary) OVER(ORDER BY Id) AS 'Running Total' FROM #Employee

 

SELECT e1.*,(SELECT SUM(Salary) FROM #Employee e2 WHERE e1.id >= e2.id) AS 'Running Total' 

FROM #Employee e1

--Note - For Running total, required column each row value must be unique. In OVER () clause or WHERE clause.

Ranking functions in sql

 CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

SELECT
       Salary
       , ROW_NUMBER() OVER(ORDER BY Salary ASC) AS 'ROWNUMBER'
       , RANK() OVER(ORDER BY Salary ASC) AS 'RANK'
       , DENSE_RANK() OVER(ORDER BY Salary ASC) AS 'DENSERANK'
       , ROW_NUMBER() OVER(PARTITION BY Salary ORDER BY Salary ASC) AS 'PARTITIONBY'
       , NTILE(3) OVER(ORDER BY Salary) AS 'NTILE'

 FROM #Employee

1. ROW_NUMBER() - Serial Number for each row.
2. RANK() - Rank each row and same record assign same rank and next rank number will be addition of the previous rank (same rank).
3. DENSE_RANK() - Rank each row and same record assign same rank and next rank number would be assigned the next rank value.
4. NTILE(3) - Groups the row based on number given.

Select 2nd highest salary in sql

CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)
 
SELECT * FROM #Employee

 

--2nd highest

SELECT MAX(Salary) FROM #Employee WHERE Salary < (SELECT MAX(Salary) FROM #Employee)

 

--N number highest using subquery
--Change highlighted color number to get the N number of highest
SELECT TOP 1 *
FROM (
       SELECT DISTINCT TOP 2 Salary
       FROM #Employee
       ORDER BY Salary DESC
) A
ORDER BY Salary ASC
 
--OR
 
SELECT DISTINCT Salary
FROM #Employee Emp1
WHERE 2 = (
       SELECT COUNT(DISTINCT Emp2.Salary)
       FROM #Employee Emp2
       WHERE Emp2.Salary >= Emp1.Salary

)

 

--N number highest using CTE

--Change highlighted color number to get the N number of highest
;WITH RESULT
AS
(
       SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS 'DENSERANK'
       FROM #Employee
)

SELECT TOP 1 * FROM RESULT WHERE DENSERANK = 2 

--OFFSET

--Change highlighted color number to get the N number of highest
SELECT DISTINCT Salary
FROM #Employee
ORDER BY Salary DESC
OFFSET 2-1 ROWS

FETCH NEXT 1 ROWS ONLY