Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Separate Float into Integer and Fractional Parts

Posted on 2011-03-22
Medium Priority
5,000 Views
I need to separate a floating point decimal into 2 integers, one that represents the whole number part, and one that represents the factional part.  For example, if the float is 10.25, I need to get 10 and 25.

The first part is easy, CONVERT(INT, [floating number])

However, I can't figure out the fractional part because there can be any number of digits after the decimal.

Both [floating number]%(CONVERT(INT, [floating number])) and [floating number] - (CONVERT(INT, [floating number])) will remove the whole number part, leaving me with 0.25 in my example.  My challenge is scaling 0.25 up to 25, without knowing that there are only 2 digits after the decimal.

I hope that's not too repetitive.  Any help would be much appreciated.  Thanks.
0
Question by:DBag1
• 2

LVL 18

Expert Comment

ID: 35191364
for positive numbers, including whole numbers

``````--works for positive numbers only, for negative would need some thought as to
-- how it should work

DECLARE @VAL as float;
SET @VAL = 3.14159265;
--SET @VAL = 3;
DECLARE @DEC AS varchar(50);
SET @DEC = CAST((@VAL - FLOOR(@VAL)) AS VARCHAR);
IF CHARINDEX('.', @DEC) > 0
SET @DEC = SUBSTRING(@DEC, CHARINDEX('.',@DEC) + 1, LEN(@DEC) - CHARINDEX(@DEC,'.'));

SELECT CAST(FLOOR(@VAL) AS INTEGER) AS THEINT, @DEC THEREST
``````
0

LVL 26

Expert Comment

ID: 35191367
DECLARE @N1  int
DECLARE @N2  int

SELECT @N1 = ROUND([floating number],0)
SELECT @N2 = ROUND([floating number] - @N1, 2)

0

LVL 18

Accepted Solution

deighton earned 2000 total points
ID: 35191413
``````--works for -ve numbers

DECLARE @VAL as float;
SET @VAL = -27.3344333;
--SET @VAL = 3;

DECLARE @SGN AS INTEGER;
SET @SGN = 1;
IF @VAL<0
SELECT @SGN = -1, @VAL = 0 - @VAL;

DECLARE @DEC AS varchar(50);
SET @DEC = CAST((@VAL - FLOOR(@VAL)) AS VARCHAR);
IF CHARINDEX('.', @DEC) > 0
SET @DEC = SUBSTRING(@DEC, CHARINDEX('.',@DEC) + 1, LEN(@DEC) - CHARINDEX(@DEC,'.'));

SELECT @SGN * CAST(FLOOR(@VAL) AS INTEGER) AS THEINT, @DEC THEREST
``````
0

Author Closing Comment

ID: 35191482
Thanks!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month15 days, 2 hours left to enroll