Format float as varchar

Posted on 2006-06-22
Last Modified: 2010-08-05
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?

Question by:cdfllc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2

Assisted Solution

kenpem earned 250 total points
ID: 16964395
try this:

select my_ref, my_id, ( +'('+  ltrim(str(my_ref,30,20)) + ') '+ name) as my_ref_name from my_table

you may have to play with the number of decimals!
LVL 32

Expert Comment

ID: 16964488
You have run into one of the many limitations of using float and real numbers, and one reason why I always use numeric/decimal instead.  Consider this test script:

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


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.

Expert Comment

ID: 16964489
If this is something you do often, you could even

  SET @Res=LTRIM(STR(@This,30,20))
  WHILE RIGHT(@Res,1) = '0'
    SET @RES=LEFT(@Res,LEN(@Res)-1)

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 !
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

LVL 32

Expert Comment

ID: 16964538
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.

Expert Comment

ID: 16964558
oh the joys of FLOAT!

Author Comment

ID: 16969312
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
LVL 32

Accepted Solution

bhess1 earned 250 total points
ID: 16970733
How did you define the decimal field/ conversion?  

I would probably look over the values in the field, and see what the values were when converted to Decimal(19,6) (this means up to 19 digits, with 6 allowed after the decimal point) or Decimal (19,8).  If the largest actual number of positions after the decimal point was only 3 (as in your example), I would probably define the field (or Convert statement) as Decimal(9,3).  This would allow for 6 digits before the decimal point, and 3 after, e.g.:

SELECT Cast(my_ref as Decimal(9,3))

Author Comment

ID: 17001624
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex SQL Server WHERE CLause 9 40
Format Output of Select Statement 2 38
rolling count by date, hour query 7 31
Use SSRS to email customers? 4 29
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question