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.
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.
ReplyDeleteJavin
10 Basic MySQL command for application developer
According to msdn, format 128 is deprecated and might not be supported in future releases.
ReplyDeleteI 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.
ReplyDeleteYour solution works great, all decimals are there, nothing is rounded up or down, it is just perfect as it should be.
How about using the STR() function...
ReplyDeleteLTRIM(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.
thanks, format 128 works ok, not 0 are added to end of the string, like using str
ReplyDeletethis wont work for large float value
ReplyDeletePosted by : Amol Yadav
ReplyDeleteNot working for
declare @a as varchar(1000)
declare @b as float(35)
set @b=2000000
select convert(varchar(100),@b,128)
result :
2.0E6
tks a lot, worked perfectly!
ReplyDeletePlease try it with @floatVal = 15436544365.150,value will return with scientific notation
ReplyDelete