cdfllc
asked on
Format float as varchar
Hi, I have searched EE, but I must not be searching the correct keywords, as I can't find an answer to this one.
I have a column of type float "my_ref"
and I am just trying to append the value of this column to the value in the "name" column - which is a varchar
select my_ref, my_id, ( +'('+ cast(my_ref as varchar(255)) + ') '+ name) as my_ref_name from my_table
Sample data:
my_ref name
7.0010000000000003 The name
So when I run the query, I would expect the following (or I want this to happen):
(7.0010000000000003) The name
But instead, I get this:
(7.001) The name
Is there a way that I can get these results without changing the datatype from float?
If not, what bad things will happen if I change the datatype?
thanks!
I have a column of type float "my_ref"
and I am just trying to append the value of this column to the value in the "name" column - which is a varchar
select my_ref, my_id, ( +'('+ cast(my_ref as varchar(255)) + ') '+ name) as my_ref_name from my_table
Sample data:
my_ref name
7.0010000000000003 The name
So when I run the query, I would expect the following (or I want this to happen):
(7.0010000000000003) The name
But instead, I get this:
(7.001) The name
Is there a way that I can get these results without changing the datatype from float?
If not, what bad things will happen if I change the datatype?
thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If this is something you do often, you could even
CREATE FUNCTION fFloatToStr(@This FLOAT) RETURNS VARCHAR(30) AS
BEGIN
DECLARE @Res VARCHAR(30)
SET @Res=LTRIM(STR(@This,30,20 ))
WHILE RIGHT(@Res,1) = '0'
SET @RES=LEFT(@Res,LEN(@Res)-1 )
RETURN @Res
END
and then, for example
select my_ref, my_id, ( +'('+ dbo.fFloatToStr(my_ref) + ') '+ name) as my_ref_name from my_table
which is a little more readable.
Watch out for floats! They can be horribly inaccurate at the kind of precision you are working at there. Your example seems to be OK, but simply converting 7.001000000005 to a string yields a result of 7.0010000000049999 !
CREATE FUNCTION fFloatToStr(@This FLOAT) RETURNS VARCHAR(30) AS
BEGIN
DECLARE @Res VARCHAR(30)
SET @Res=LTRIM(STR(@This,30,20
WHILE RIGHT(@Res,1) = '0'
SET @RES=LEFT(@Res,LEN(@Res)-1
RETURN @Res
END
and then, for example
select my_ref, my_id, ( +'('+ dbo.fFloatToStr(my_ref) + ') '+ name) as my_ref_name from my_table
which is a little more readable.
Watch out for floats! They can be horribly inaccurate at the kind of precision you are working at there. Your example seems to be OK, but simply converting 7.001000000005 to a string yields a result of 7.0010000000049999 !
One additional comment. The value 7.001 is actually what was inserted into the column. Try it and see -- a value of 7.001 inserted returned 7.0010000000000003 from a float field for me.
oh the joys of FLOAT!
ASKER
Yes, in the defense of the person that built this application originally, I don think this field is being used for it's original purpose.
But I will know for any future enhancements to stay away from float.
So can any of you guys help me convert to decimal?
I just went and changed the datatype of the field, from float to decimal (not on the production table, of course) but now it has removed all the numbers after the decimal place...
So what was originally 7.001 is now 7
But I will know for any future enhancements to stay away from float.
So can any of you guys help me convert to decimal?
I just went and changed the datatype of the field, from float to decimal (not on the production table, of course) but now it has removed all the numbers after the decimal place...
So what was originally 7.001 is now 7
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys/gals - I used a combination of your answers:
select ( +'('+ convert(nvarchar, Cast(my_ref as Decimal(9,3) )) + ') '+ name) name from mytable
This gives results such as this:
(700.159) The name
select ( +'('+ convert(nvarchar, Cast(my_ref as Decimal(9,3) )) + ') '+ name) name from mytable
This gives results such as this:
(700.159) The name
SET NOCOUNT ON
CREATE TABLE #Test1 (f1 float)
INSERT INTO #Test1 VALUES (15.0003)
INSERT INTO #Test1 VALUES (15.00003)
INSERT INTO #Test1 VALUES (15.000003)
INSERT INTO #Test1 VALUES (15.0000003)
INSERT INTO #Test1 VALUES (15.00000003)
INSERT INTO #Test1 VALUES (15.000000003)
SELECT f1, Cast(f1 AS varchar(16)) AS CastF1,
convert(varchar(16), f1) AS ConvF1,
convert(varchar(16), f1, 1) AS [ConvF1-1]
FROM #Test1
DROP TABLE #Test1
When run, the output of the script is:
f1 CastF1 ConvF1 ConvF1-1
--------------------------
15.000299999999999 15.0003 15.0003 1.5000300e+001
15.000030000000001 15 15 1.5000030e+001
15.000003 15 15 1.5000003e+001
15.0000003 15 15 1.5000000e+001
15.000000030000001 15 15 1.5000000e+001
15.000000003 15 15 1.5000000e+001
Here, you see two sources of imprecision: rounding errors and Cast/Convert limitations. I assume that you already know about rounding issues, so let's talk about Cast/Convert limitations.
As you see, only the 15.0003 value has all of its digits shown in Cast, or in Convert with no Format specified. In the specified format 1 in convert, you can see the value out to 8 total digits, but no more. This is actually covered (obscurely) in BoL, which states (in part):
This table shows the style values for float or real conversion to character data.
Value Output
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
So, by default, CAST and CONVERT will only show six digits of the FLOAT value, no matter what precision it has. CAST will never show anything else, but CONVERT can be made to show up to 16 digits, but only in scientific notation.
This is not a bug, but (IMO) a bad design decision.