### SQL SERVER: Separate Integer and Fractional part from Decimal Number

Introduction: In this article I am going to share how to split or separate integer and fractional portion from numeric or decimal number in sql server.

Implementation: Let’s create some examples to understand how to get this.

Suppose we have student marks  in decimal and we want to split integer and fractional part from it then we can write the query as:

DECLARE @Marks DECIMAL(18,2)=70.50
--OR DECLARE @Marks NUMERIC(18,2)=70.50
--OR DECLARE @Marks VARCHAR(10)='70.50'

First Way:
SELECT LEFT(@Marks, CHARINDEX('.', @Marks)-1) IntegerPart,
RIGHT(@Marks, LEN(@Marks)-CHARINDEX('.', @Marks)) FractionPart;

Second  Way:
SELECT LEFT(@Marks,CHARINDEX('.',@Marks)-1) IntegerPart,
RIGHT(@Marks,CHARINDEX('.',REVERSE(@Marks)) - 1)FractionPart

Result:
 IntegerPart FractionPart 70 50

Explanation:
1. CHARINDEX is used to find the position of the comma in the string.
2. Based on the CHARINDEX of the comma the Left function pulls everything left of the indicated position
3. Right pulls everything to the right of the indicated position, which is the length of the string minus the position of the comma.
Now let’s see how to use the above query in table data.Let’s create a table and add some dummy data into it using the following script.

CREATE TABLE #tbItems
(
Id INT IDENTITY(1,1),
ItemName VARCHAR(100),
Price DECIMAL(18,2)
)
INSERT INTO #tbItems VALUES
('Chair',600.50),
('Table',7600.50),
('Almirah',15000.00),
('Bed',1600.80),
('Sofa Set',1200.20)

We have item names and their prices and suppose we want to show price in rupees and paisa in different columns the we can write the query as:

SELECT ItemName, Price, LEFT(Price,CHARINDEX('.',Price)-1) AS Rupees,
RIGHT(Price, LEN(Price)-CHARINDEX('.', Price)) AS 'Paisa' FROM #tbItems

Result:
 ItemName Price Rupees Paisa Chair 600.50 600 50 Table 7600.50 7600 50 Almirah 15000.00 15000 00 Bed 1600.80 1600 80 Sofa Set 1200.20 1200 20

Now over to you: