Problem in decimal fraction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem in decimal fraction

Author

Topic

abhi143
Starting Member

32 Posts

Posted - 2006-06-17 : 06:00:43

have three STRING data type fields...

suppose in first field the value is = 45.125455
and in second field the value is = .4545
Now the multiplication of first and second field will come in third field.
and the third field, result is = 20.5095192975

Now i want to display in third field only = 509519(After decimal..up to 6 digit) by using stored Procedure

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-17 : 08:16:54

quote:
Originally posted by abhi143

Now i want to display in third field only = 509519(After decimal..up to 6 digit) by using stored Procedure

What you most likely would want to use is

-- prepare test data
declare @num table (i varchar(50))

insert @num 
select '20.509519297500000'

-- do the work
SELECT	i,
	substring(i, charindex('.', i) + 1, 6)
FROM	@num

Please not that this does not do a correct round off. It clips the string.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 13:35:06

[code]
select
num,
dec =floor((num-floor(num))*1000000)
from
(select num = 45.125455*.4545) a[/code]
Results:
[code]num dec
--------------- ----------------------------------
20.5095192975 509519

(1 row(s) affected)[/code]

CODO ERGO SUM

abhi143
Starting Member

32 Posts

Posted - 2006-06-19 : 01:58:14

Thanks michael..it works

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-19 : 02:47:31

quote:
Originally posted by abhi143

Thanks michael..it works

Really? Michael and Abhi, try Michael's solution with a negative number or a product having less than 6 decimal digits.

select
	num,
	dec = floor((num - floor(num)) * 1000000)
from
	(select num = -45.125455 * .4545 union all select 4.012 * .5 union all select -4.012 * .5) a


num             dec
--------------  ------
-20.5095192975	490480
2.0060000000	6000
-2.0060000000	994000

and compare to

-- prepare test data
declare @num table (i varchar(50))

insert @num 
select '20.509519297500000' union all
select '20' union all
select '-13' union all
select '15.' union all
select '2.006' union all
select '.05' union all
select '234.1' union all
select '-69.09' union all
select '-20.509519297500000'

-- do the work
SELECT	i,
	case when charindex('.', i) = 0 then '' else substring(i, charindex('.', i) + 1, 6) end
FROM	@num


i                    (no column name)
-------------------  ----------------
20.509519297500000   509519
20
-13
15.
.05                  05
234.1                1
2.006                006
-69.09               09
-20.509519297500000  509519

Peter Larsson
Helsingborg, Sweden

abhi143
Starting Member

32 Posts

Posted - 2006-06-22 : 02:14:42

yes..peso...i found bug in Michael case..after applying some other data..Now i have very much comfortable in u r case.

thanks both of u

abhi143
Starting Member

32 Posts

Posted - 2006-06-22 : 04:27:51

still iam facing problem in Peso and Michael Script..

In Peso.. script..it will not display up to 6 digit...
for example- 2.006 006(Wrong)
it should be 2.006 006000(correct)

Plz help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 06:45:47

quote:
Originally posted by abhi143

for example- 2.006 006 (Wrong)
it should be 2.006 006000 (correct)

Plz help me

Just add some zeros to the end of the code!

-- do the work
SELECT	i,
	case when charindex('.', i) = 0 then '000000' else left(substring(i, charindex('.', i) + 1, 6) + '000000', 6) end
FROM	@num

In your original posting, you wrote

quote:
(After decimal..up to 6 digit)

You should have written always six digits.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 06:55:28

Here is a generic solution that allows you to choose any number of digits.

-- prepare test data
declare @num table (i varchar(50))

insert @num 
select '20.509519297500000' union all
select '20' union all
select '-13' union all
select '15.' union all
select '2.006' union all
select '.05' union all
select '234.1' union all
select '-69.09' union all
select '-20.509519297500000'

declare @digits tinyint

-- do the work

select @digits = 6

SELECT	i,
	case when charindex('.', i) = 0 then replicate('0', @digits) else left(substring(i, charindex('.', i) + 1, @digits) + replicate('0', @digits), @digits) end
FROM	@num

Peter Larsson
Helsingborg, Sweden

abhi143
Starting Member

32 Posts

Posted - 2006-06-23 : 05:13:50

can we create a function for that logic..???????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:15:10

quote:
Originally posted by abhi143

can we create a function for that logic..???????

Yes. Why not.

abhi143
Starting Member

32 Posts

Posted - 2006-06-23 : 05:16:58

A function, in which user have a flexibility that as per his choice he can take the digit after decimal..

i.e it can be 4 digit or 6 digit or 2 digit

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:30:30

quote:
Originally posted by abhi143

A function, in which user have a flexibility that as per his choice he can take the digit after decimal..

i.e it can be 4 digit or 6 digit or 2 digit

Just create the function with 2 input parameters, the 1st is the varchar and the 2nd is the @digits

abhi143
Starting Member

32 Posts

Posted - 2006-06-23 : 05:39:48

can you give the example for that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:53:12

it is very simple. I just take Peso's code and change the column i into a variable @i


-- Check if the function exists, drop it
if exists (select * from sysobjects where xtype = 'FN' and name = 'fn_test') drop function dbo.fn_test
go
-- Create the function

create function dbo.fn_test
(
	@i	varchar(50),   
    	@digits	int
)
returns varchar(10)
as
begin
	return
	(
		select case when charindex('.', @i) = 0 then replicate('0', @digits) else left(substring(@i, charindex('.', @i) + 1, @digits) + replicate('0', @digits), @digits) end
	)
end
go

-- prepare test data
declare @num table (i varchar(50))

insert @num 
select '20.509519297500000' union all
select '20' union all
select '-13' union all
select '15.' union all
select '2.006' union all
select '.05' union all
select '234.1' union all
select '-69.09' union all
select '-20.509519297500000'

declare @digits tinyint

-- do the work

select @digits = 6

SELECT	i,
	case when charindex('.', i) = 0 then replicate('0', @digits) else left(substring(i, charindex('.', i) + 1, @digits) + replicate('0', @digits), @digits) end,
	dbo.fn_test(i, @digits) as [fn_test]
FROM	@num

abhi143
Starting Member

32 Posts

Posted - 2006-06-23 : 06:13:32

thanks KHTAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources