What would be a reliable way to get fractional value from a number?

Question

I have a number of type Decimal(8, 2) and have been using Substring to get fractional value.

E.g.)
    declare @val decimal(8, 2), @strVal varchar(10)
    set @val = 15.80
    set @strVal = cast(@val as varchar)
    select  @val, substring(@strVal, charindex('.', @strVal), len(@strVal))

Is there a better way to simply get fractional value, .80 from @val without having to convert it to a character?
I am wondering if there is a built-in UDF (User-Defined Function) that would parse instead of having to rolling out my own version.

Your question makes no sense. you have a decimal and you're trying to convert it to a decimal???? — JonH, Dec 07 '09 at 15:10
What ??! You already have the decimal value, in @val. What the substring is doing is retreiving the fractional value as a character value. — Charles Bretana, Dec 07 '09 at 15:11
Yes, fractional value of type `int` is what I am trying to get out of it. So from 15.80, I would like to get `.80` — dance2die, Dec 07 '09 at 15:12

score 10 · Accepted Answer · answered Dec 07 '09 at 15:17

10

Use the modulus (%) operator. You'll get 0.80.

Like this:

declare @val decimal(8, 2)
set     @val = 15.80
select  @val, @val % 1

answered Dec 07 '09 at 15:17

Nathan Wheeler

5,896
2
29
48

Ha, expected nothing less from a guy with a handle, `md5sum`. Thanks. – dance2die Dec 07 '09 at 15:31
Not sure why people continuously overlook the modulus operator... I've seen some REALLY bizarre ways of stripping whole numbers off of decimal values. – Nathan Wheeler Dec 07 '09 at 15:47

score 2 · Answer 2 · answered Dec 07 '09 at 15:13

2

I think you mean the fractional value, not the decimal value. You already have teh decimal value. To get the fractional value, use Round, or Floor functions

  Declare @Fraction Decimal(8,2)
  Set @Fraction = @Val - Floor(@Val)

or

  Set @Fraction = @Val - Round(@Val, 0)

answered Dec 07 '09 at 15:13

Charles Bretana

143,358
22
150
216

I haven't explored *mathematical* functions, yet and this looks like a simply but yet reliable way to get a fractional number. Thanks Charles. – dance2die Dec 07 '09 at 15:15
No problem, check out the Round, and the floor reference links, to see how they work and the diofferences between them... – Charles Bretana Dec 07 '09 at 15:18

Daniel Vassallo · Answer 3 · 2009-12-07T15:21:11.467

1

SET @val = (@val - ROUND(@val, 0, 1) * 100)

The ROUND(@val, 0, 1) should truncate the 15.80 into a 15.00. Then you subtract the 15 from the 15.80, and multiply the 0.80 by 100 to get 80 in numeric form.

The ROUND function requires the third parameter set to 1 to truncate the number. Otherwise, it would convert 15.80 into 16.00. See: http://msdn.microsoft.com/en-us/library/ms175003.aspx.

edited Dec 07 '09 at 15:21

answered Dec 07 '09 at 15:14

Daniel Vassallo

337,827
72
505
443

score 0 · Answer 4 · edited May 23 '17 at 12:07

For those that want to return the fractional value only... (i.e. without the decimal) SQLMenace posted a great way to do this using PARSENAME from this Article: How To Get Only Numbers After Decimal

An IMPORTANT NOTE before use: If their is no decimal SELECT PARSENAME((22),2) will return NULL... And SELECT PARSENAME((22 ),1) will return only the whole number... This can easily be handled by COALESCE or checking for a zero modulus... but it may not be practical in all uses

SELECT PARSENAME((22.777),1) -- Returns ONLY The number after decimal
SELECT PARSENAME((22.777),2) -- Returns ONLY The whole number

What would be a reliable way to get fractional value from a number?

4 Answers4

Linked