Function to get decimal values from numeric field? 1

[chriscboy]

Hi All,

Is there a function that will return the decimal portion of a number in SQL Server?

e.g. passing 123.45 would return 45

If there is what is it called, or do I have do something manual, like converting to a string and grabbing all chars after the decimal place?

Thanks

Chris

 

[earthandfire]

PARSENAME is one possible solution.


Hope this helps.

 

[philhege]

This works for two places:

declare @number numeric(6,2)
set @number = 123.45
select cast((abs(@number) - floor(abs(@number))) * 100 as int)

You'd have to work out a method to accept any decimal length.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[chriscboy]

Thanks for your suggestions. I went down the route of using
PARSENAME to return the information I wanted.

 

[ESquared]

For some reason parsename bothers me as the solution because it must convert to varchar first, and is basically misusing a system function (though it does work).

I did some testing with three methods:

create table #nums (
	num numeric(10,5)
)

set nocount on
declare @i int
set @i = 100000 - (select count(*) from #nums)
while @i > 0 begin
	insert #nums select rand() * 200000 - 100000
	set @i = @i - 1
end

go
/*1*/ select num, parsename(num, 1) from #nums
go
/*2*/ select num, substring(convert(varchar(13), num), charindex('.', convert(varchar(13), num)) + 1, 5) from #nums
go
/*3*/ select num, convert(int, (abs(num) - floor(abs(num))) * 100000) from #nums
go

The third method loses leading zeroes, so 1.01234 gives '01234' (a string) in the first two and 1234 (an integer) in the third. Adding a string conversion function slows it down.

Using SQL Profiler what I found is that the moment you get into string conversion, CPU cost goes up significantly:

query 1: CPU 500, Duration 672
query 2: CPU 672, Duration 750
query 3: CPU 313, Duration 640


May I ask what you're using this for? Is it possible the client can do this formatting work?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ShankarJ]

Hi!

Isn't that what the % (modulo) operator is for?

SELECT num, ((num * 100) % 100) as decimalpart ...

Regards

 

[ESquared]

You can't use modulo on anything but int datatype in SQL server. That was the first thing I tired.

Also, modulo for negative numbers is undefined: different programming languages give different results.

Is -.25 % 8 = -0.25 or is it = 7.75? I know what the right answer is mathematically, but programming languages don't always honor this, and I can see the logic behind the other answer as well.