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

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

E.g.)
    declare @val dec开发者_运维问答imal(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.

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

Like this:

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

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)

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.

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