How to copy float(8) column into varchar(10)

• rwaqas

  SSC Enthusiast

  Points: 137

  More actions

  November 17, 2009 at 10:52 pm

  #214106

  Hi guys,

  Is it possible that I can copy a column whose data type is float(8) to a column in another table whose data type is varchar(10)

  I attempted to update it using

  update p

  set p.vphone = left(fp.phone,10)

  from PMPAXFT P join drkhandb.dbo.finalplans FP

  on p.VURL = fp.planskey

  and vphone is not null

  But using this it actually updated value which was in float as '6464737169' to varchar value 6.46474e+0

  Did tried type casting and conversion too but with no success.

  Thanks,

  Waqas.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  November 19, 2009 at 3:44 am

  #1081442

  Hi

  i tried this....

  create table t2 (col1 float(8))

  insert into t2 values (23423889777777)

  select left(col1,8) from t2 - returns "2.34239e"

  select left(cast(col1 as numeric(21,2)),8) from t2 - returns 23423889.

  "Keep Trying"

• rwaqas

  SSC Enthusiast

  Points: 137

  More actions

  November 19, 2009 at 3:53 am

  #1081448

  Thanks ChiragNS.

• Saurabh Dwivedy

  SSCommitted

  Points: 1887

  More actions

  November 22, 2009 at 12:38 am

  #1082701

  You can try this too...

  convert the float value to int and then convert the resultant to varchar

  consider this:

  /*Assuming you have a float value*/

  declare @f float = 12345678

  select convert(varchar(10), convert(int, @f))

  One caveat is that it will not work if you have decimal embedded in the float value (such as 1234.5678) and such like.

  This will convert the float into the varchar value you are seeking.

  Regards

  Saurabh.

  Saurabh Dwivedy
  ___________________________________________________________

  My Blog: http://tinyurl.com/dwivedys

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537

  Be Happy!

• rwaqas

  SSC Enthusiast

  Points: 137

  More actions

  November 22, 2009 at 11:05 pm

  #1082809

  Thanks Saurabh,

  Thats how I finally manage to accomplish the desired output:

  set p.vphone = CONVERT(varchar(100), CAST(fp.phone AS decimal(38,0)))

  from PMPAXFT P join cmdlhrdev117.drkhandb.dbo.finalplans FP

  on p.VURL = fp.planskey

  and p.vphone is not null

  and p.vphone like '%e%'

  Regrads,

  Waqas