January 12, 2009 at 1:40 pm
I have a data that I import (XLS) to SQL server that's integer and when its dumped in the server, the field is using 'Float'.
I have a number for instance 123456789 but when I do a len(x) on that field, it's giving me a 12 which is not correct because it's obviously a 9.
So how do I fix this so I can get the actual length of the field and not the 'float' length?
Thanks.
January 12, 2009 at 1:42 pm
Cast/convert to varchar, check the len on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 1:48 pm
I did that already. I even changed the field to varchar(100) with no success.
January 12, 2009 at 2:06 pm
The following query will give an idea of why len is returning 12 and how to get it to return 9.
select len('123456789 ') IfVarchar, (cast(cast('123456789 ' as float) as varchar)) FloatStoredAsChar
, len(cast(cast('123456789 ' as float) as varchar)) LenOf_FloatStoredAsChar,
len(cast(cast('123456789 ' as float) as int)) LenOf_FloatStoredAsInt
Converting to int might help.
---- [font="Tahoma"]Live and Let Live![/font] ----
January 12, 2009 at 2:07 pm
OK I found a quick solution for those who comes across this issue.
Needs to cast the field as BIGINT then use FLOAT to remove the decimal to get the actual length of the float.
len(FLOOR(cast(field as BIGINT)))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply