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)

How to remove last comma from string in sql server

DECLARE @STRING VARCHAR(50) = 'ABC,XYZ,'

SELECT IIF(CHARINDEX(',',REVERSE(@STRING))=1,LEFT(@STRING,LEN(@STRING)-1),@STRING)

OR

SELECT CASE 
           WHEN CHARINDEX(',',REVERSE(@STRING))=1 
           THEN LEFT(@STRING,LEN(@STRING)-1) 
           ELSE @STRING 
       END

How to get sp_executesql result into a variable?

--Way 1:-

DECLARE @Query NVARCHAR(4000)

       ,@TodayDate NVARCHAR(1000)

SET @Query = N'SELECT @TodayDate = GETDATE()'

EXEC SP_EXECUTESQL @Query, N'@TodayDate NVARCHAR(1000) OUTPUT', @TodayDate = @TodayDate OUTPUT

SELECT @TodayDate AS TodayDate

--Way 2:-

CREATE TABLE #FruitName(Id INT IDENTITY,Name VARCHAR(100))

INSERT INTO #FruitName(Name) VALUES('Apple')

INSERT INTO #FruitName(Name) VALUES('Mango')

INSERT INTO #FruitName(Name) VALUES('Banana')

INSERT INTO #FruitName(Name) VALUES('Pineapple')

DECLARE  @Query NVARCHAR(4000)

        ,@Name NVARCHAR(1000)

        ,@Id NVARCHAR(1000)

SET @Id = '1'

SET @Query = N'SELECT @Name = Name FROM #FruitName WHERE Id = '+@Id+''

EXEC sp_executesql @Query, N'@Name NVARCHAR(1000) OUTPUT', @Name = @Name  OUTPUT

SELECT @Name AS Name

--Way 3:- Using Placeholder

DECLARE  @Query NVARCHAR(4000)

        ,@Name NVARCHAR(1000)

        ,@Id NVARCHAR(1000)

SET @Id = '1'

SET @Query = N'SELECT @Name = Name FROM #FruitName WHERE Id = @Id'

EXEC sp_executesql @Query, N'@Id NVARCHAR(1000), @Name NVARCHAR(100) OUTPUT', @ID = @ID, @Name = @Name OUTPUT

SELECT @Name AS Name

--Way 4:- Using Temp Table

DECLARE @Id NVARCHAR(1000)

SET @Id = '1'

CREATE TABLE #Name(Name VARCHAR(100))

INSERT INTO #Name EXEC('SELECT Name FROM #FruitName WHERE Id = '+@Id+'')

SELECT * FROM #Name

DROP TABLE #Name

--Way 5:- Using Table Variable

DECLARE @Id NVARCHAR(1000)

SET @Id = '1'

DECLARE @FruitName AS TABLE(Name VARCHAR(100))

INSERT INTO @FruitName EXEC('SELECT Name FROM #FruitName WHERE Id = '+@Id+'')

SELECT * FROM @FruitName

--Drop Temp Table

DROP TABLE #FruitName

How to make EMI Loan Amortization in SQL server

DECLARE

 @LoanAmount MONEY = '5000000.00'

,@ROI FLOAT = 11

,@Tenure NUMERIC(18,2) = 12

,@Period INT

,@Principal MONEY

,@Interest MONEY

,@EMI MONEY

,@AMT MONEY

SET @Period = 1

SET @ROI = (@ROI/100)/12

DECLARE @EMI_TABLE TABLE

(PERIOD INT,PAYDATE SMALLDATETIME,EMI MONEY,PRINCIPAL MONEY,INTEREST MONEY ,BALANCE MONEY)  

--EMI = [P x R x (1 + R)^N] / [(1 + R)^N - 1]

SELECT @EMI = ROUND((@LoanAmount * @ROI * POWER((1 + @ROI), @Tenure))  / (POWER((1 + @ROI), @Tenure) - 1),2)

WHILE @Period <= @Tenure

BEGIN

      --INTEREST = [P x R]

      SELECT @Interest = ROUND((@LoanAmount * @ROI),2)

      --PRINCIPAL = [EMI - INTEREST]

      SELECT @Principal = ROUND((@EMI - @Interest),2)

      --OUTSTANDING LOAN AMOUNT = [@LoanAmount - Principal]

      SELECT @LoanAmount = ROUND((@LoanAmount - @Principal),2)

      INSERT INTO @EMI_TABLE VALUES(@Period,DATEADD(MM,@Period,GETDATE()),@EMI,@Principal,@Interest,@LoanAmount)

      SET @Period = @Period + 1

END

SELECT * FROM @EMI_TABLE

Output

Note:-

P - Loan Amount
R - Rate of Interest
N - Tenure/Loan Period

You will get different in paisa. Need to manage as per requirement.

Calculate Loan Amount from EMI in SQL server

DECLARE

 @LoanAmount MONEY = '7537500.00'

,@ROI FLOAT = 11

,@Tenure NUMERIC(18,2) = 12

,@EMI MONEY

,@AMT MONEY

SET @ROI = (@ROI/100)/12

--EMI = [P x R x (1 + R)^N] / [(1 + R)^N - 1]

SELECT @EMI = ROUND((@LoanAmount * @ROI * POWER((1 + @ROI), @Tenure))  / (POWER((1 + @ROI), @Tenure) - 1),3) 

SELECT @EMI

Output(EMI)

666176.751

First Query return the monthly installment of Loan Amount i.e. EMI.

--AMT = [EMI x (1 + R)^N - 1] / [R x (1 + R)^N]

SELECT @AMT = ROUND(@EMI * (POWER((1 + @ROI), @Tenure) - 1) / (@ROI * POWER((1 + @ROI), @Tenure)),2)

SELECT @AMT

Output

7537500.00

Second Query return the Loan Amount based on your monthly installment(EMI).

Note:-

P - Loan Amount
R - Rate of Interest
N - Tenure/Loan Period
EMI - Estimated Monthly Installment


Depend upon your requirement, you may change round off figure.