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.

In previous articles i explained How to Split string from comma and get left and right part and Convert or split comma separated string into table rows in sql server and Using coalesce to get column values as comma separated list in sql server and Concatenate first, middle and last name in sql server and Using case expression in select statement

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:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.        

• Tweet
• Share
• Share
• Share

Related Post