March 4, 2010 at 8:11 am
( This is inccorectly posted to SQL 2000 thread)
Hi,
I am trying to write a function converting any float to varchar(50) without lossing any disgits:
(1) Select col1_float, float_to_vc50(col1_float) from my table
(2) Sample column data:
3886.0559
112.097
66.8879434336119
9999999.1234567890123456789012345678901
( note that the total number of digits for float is 38)
I can try out your function on my data
I think the key problem is declaration demical(m,n) cannot take variables
so we stuck with fix precision and no quick way to adjust.
Thanks
Jack
March 4, 2010 at 8:54 am
This is just a stab at this but I think it is the right direction.
See these links:
Explanation of Mantissa and Precision
It seems that floats do not have 38 digits as you thought. If you declare float @f(53), that will give you approximately 7 digits of precision (what is after the decimal point), declare @f float(53)
set @f=9999999.1234567890123456789012345678
select @f, gives you 9999999.12345679, which are 8 digits, rounded, so it is not precisely 7 digits, but you get the point (:-P).
Anyway, so what you need is to make it decimal type, which has precision of 38. Try this:
declare @f decimal(38,31)
set @f=9999999.1234567890123456789012345678
select @f, I will leave the rest of the function to you.
March 4, 2010 at 9:05 am
Thanks for your opition.
Without converting to varchar(50), we can keep more than 7
declare @f decimal(38,20)
set @f=9999999.1234567890123456789012345678
select round(@f,20)
The key problem is about converting to varchar(50). I actually wrote a function just keep 13 digits after converting to varchar(50). But it add one more digits when data is 3886.0559.
This does not seems to be a simple problem if ever possible.
Jack
March 4, 2010 at 9:17 am
I don't understand what the issue is
declare @f decimal(38,31)
set @f=9999999.123456789012345678901234567891
select round(@f,20)
select cast(@f as varchar(50))
Can you post your function and some sample data? See this link for how to do that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2010 at 9:38 am
As requested here is the code
CREATE FUNCTION [dbo].[FormatFloat](@dIn decimal(30,14),@p int)
RETURNS varchar(100)
AS
begin
if (@dIn is null) return null
declare @d decimal(30,14)
set @d=Round(@dIn,@p) --- Float precission is 15 digits
declare @intPart bigint
set @intPart= Cast (@d as bigint)
declare @strInt varchar(100) -- With Comma inserted per 1000's
set @strInt=left(convert(varchar(100), cast(@intPart as money), 1), len(convert(varchar(100), cast(@intPart as money), 1) ) - 3 )
declare @decPart decimal(30,14)
if (@d>=0) set @decPart=@d-@intPart
else set @decPart=@intPart-@d
set @decPart=Round(@decPart,@p)
declare @strDec varchar(100)
if (@decPart=0) set @strDec='.00'
else
begin
declare @bInt varchar(100)
set @bInt= Reverse(cast(Replace(Reverse(cast(@decPart as Varchar(100))),'.0','') as bigInt))
set @strDec='.'+@bInt
if len(@strDec)=2 set @strDec=@strDec+'0'
end
declare @ret varchar(100)
set @ret = @strInt+@strDec
if (@d<0) set @ret = '('+Replace(@ret,'-','')+')'
return @ret
end
March 4, 2010 at 10:53 am
No disrespect but a lot of what you are doing is unnecessary. With many conversions back and forth it is easy to get messy results.
Try this
Create FUNCTION [dbo].[NewFormatFloat](@dIn decimal(38,31),@p int)
RETURNS varchar(100)
AS
begin
declare @ReturnString Varchar(100)
declare @LeftString varchar(50)
select @LeftString=convert(varchar, convert(money, (cast(@dIn as bigint))),1) --find left part
declare @RightString varchar(50)
select @RightString = Substring(Cast(@dIn as varchar(50)), charindex('.', Cast(@dIn as varchar(50))) + 1, @p) --find the right part, after the decimal, up to @p precision
select @ReturnString=left(@leftString, charindex('.',@leftString)) + @RightString --leftstring with decimal + right string
Return @ReturnString
END
select dbo.NewFormatFloat(9999999.1234567890123456789012345678901, 12)
select dbo.NewFormatFloat(9999999.1234567890123456789012345678901, 31) --for some reason 31 is the max precision I can set without overflow error
March 4, 2010 at 2:40 pm
Ray-SQL,
Thanks for you code. Much simpler and I will try to incorporate.
I used your function on a temp table ( float(50) or float)
create table #tmp (data float(50))
insert #tmp values(112.097)
select dbo.NewFormatFloat(data,12) from #tmp
drop table #tmp
--112.096999999999
Look like it will alter digits. Does that happen on your SQL Server.
Jack
March 4, 2010 at 2:52 pm
You are welcome.
Note the parameter of the function is Decimal (38,13), so when you pass a float to it, it go through an implicit coversion and the accuracy is lost there.
declare @data float(50)
set @data=112.097
select cast(@data as decimal(38,31))
Can you have decimal as your column format? If it has to be float then the function can just handle float, but then we are back to the beginning of this problem again. You will either have decimal type that gives you 30+ precision or float type with 15 digits precision, you can't have both ways. :crying:
March 4, 2010 at 3:06 pm
(1) I cannot change the column type. it is a vendor software and they should have used decimal(m,n)
(2) I change your function slightly, replacing decimal with Float. Still not fully working.
(3) I do not know the data in the column ahead of time. So no way to predict how many decimal digits we can defined or how many integer digits we can expect.
I think this is a dead-end problem but do let me know if you would like to try out your ideas.
Thanks
Jack
March 4, 2010 at 3:09 pm
After change function to use float(50) I got
select dbo.NewFormatFloat2(152.463616,13)
--152.464
some cutoff
Jack
March 4, 2010 at 4:19 pm
3rd time a charm? I've CAST the float to decimal first before casting to Varchar.
Create FUNCTION [dbo].[NewFormatFloat3](@dIn float(53),@p int)
RETURNS varchar(100)
AS
begin
declare @ReturnString Varchar(100)
declare @LeftString varchar(50)
select @LeftString=convert(varchar, convert(money, (cast(@dIn as bigint))),1) --find left part
declare @RightString varchar(50)
select @RightString = Substring(cast(Cast(@dIn as decimal(38, 31)) as varchar(50)), charindex('.', cast(Cast(@dIn as decimal(38, 31)) as varchar(50))) + 1, @p) --find the right part, after the decimal, up to @p precision
select @ReturnString=left(@leftString, charindex('.',@leftString)) + @RightString --leftstring with decimal + right string
Return @ReturnString
END
Anticipating you might ask how to get rid of the trailing zeros 😛 you can do this
Return select left(@returnstring, len(@returnstring) - patindex('%[^0]%',reverse(@returnstring)) + 1 ) instead of Return @ReturnString
March 4, 2010 at 5:28 pm
deployed your function #3
create table #tmp (data float(50))
insert #tmp values(112.097)
select dbo.NewFormatFloat3(data,7) from #tmp
drop table #tmp
--112.0969999
So still altering digits.
Appreciate your zero-out code.
Jack
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply