Friday, April 29, 2011

SQL Server: Float to Varchar Conversion

Recently, someone asked that, if we convert float value to varchar then result is truncated and what is the best way to get whole value converted into varchar without any truncation.
DECLARE @FloatVal FLOAT
SELECT @FloatVal = 421.765433
First lets check, how SQL Server implicitly converts float value to varchar
DECLARE @VarcharVal VARCHAR(50)
SELECT @VarcharVal = @FloatVal
SELECT @VarcharVal

OOPS. Implicit conversion from float to varchar is NOT according to our required output. Let convert it explicitly and then assign this value to varchar variable.
SELECT @VarcharVal = CAST(@FloatVal AS VARCHAR(50))
SELECT @VarcharVal
You will observe same truncated value as a result of above query. Finally,let’s try it with convert as convert supports some formatting too.
SELECT CONVERT (VARCHAR(50), @FloatVal,128)
 Yeah, that's what we were looking for.

9 comments:

  1. Wow, literally I am big fan of this kind of post, short , clear and concise and yet effective to teach something valuable to you within few seconds. Thanks a lot mate and I hope to see more and more from you. great tips keep it up.

    Javin
    10 Basic MySQL command for application developer

    ReplyDelete
  2. According to msdn, format 128 is deprecated and might not be supported in future releases.

    ReplyDelete
  3. I was trying to find a working code for a conversion from float to varchar over last two days with no success after trying dozens of solutions posted on other websites. There was always some problem.

    Your solution works great, all decimals are there, nothing is rounded up or down, it is just perfect as it should be.

    ReplyDelete
  4. How about using the STR() function...

    LTRIM(Str(float_field, length, decimal))

    Select LTRIM(Str(float_field, 15, 5))

    Note: To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

    ReplyDelete
  5. thanks, format 128 works ok, not 0 are added to end of the string, like using str

    ReplyDelete
  6. this wont work for large float value

    ReplyDelete
  7. Posted by : Amol Yadav
    Not working for
    declare @a as varchar(1000)
    declare @b as float(35)
    set @b=2000000
    select convert(varchar(100),@b,128)
    result :
    2.0E6

    ReplyDelete
  8. tks a lot, worked perfectly!

    ReplyDelete
  9. Please try it with @floatVal = 15436544365.150,value will return with scientific notation

    ReplyDelete

All suggestions are welcome