cast or convert a float to nvarchar

Question

I need to select from one column of datatype float and insert it in another column as nvarchar   I tried to cast it  cast  Column Name  as nvarchar 50    The result was 9 07235e 009 instead of a 10 digit number  phone number     Does any one know how to cast or convert this data properly

User · Answer

If you re storing phone numbers in a float typed column  which is a bad idea  then they are presumably all integers and could be cast to int before casting to nvarchar   So instead of     select cast cast 1234567890 as float  as nvarchar 50   1 23457e 009   You would use   select cast cast cast 1234567890 as float  as int  as nvarchar 50   1234567890   In these examples the innermost cast 1234567890 as float  is used in place of selecting a value from the appropriate column   I really recommend that you not store phone numbers in floats though  What if the phone number starts with a zero   select cast 0100884555 as float  100884555   Whoops  We just stored an incorrect phone number

User · Answer

Float won t convert into NVARCHAR directly  first we need to convert float into money datatype and then convert into NVARCHAR  see the examples below   Example1  SELECT CAST CAST 1234567890 1234  AS FLOAT  AS NVARCHAR 100     output   1 23457e 009   Example2  SELECT CAST CAST CAST 1234567890 1234  AS FLOAT  AS MONEY  AS NVARCHAR 100     output   1234567890 12   In Example2 value is converted into float to NVARCHAR

User · Answer

Check STR  You need something like SELECT STR  Column Name  10 0     This is SQL Server solution  for other servers check their docs

User · Answer

For anyone willing to try a different method  they can use this  select FORMAT  Column Name       from YourTable  This will easily change any float value to nvarchar

User · Answer

You can also do something  SELECT CAST CAST 34512367 392 AS decimal 30 9   AS NVARCHAR 100    Output  34512367 392000000

User · Answer

DECLARE  MyFloat  float   SET  MyFloat   1000109360 050  SELECT REPLACE  RTRIM  REPLACE  REPLACE  RTRIM   REPLACE  CAST  CAST   MyFloat AS DECIMAL  38  18    AS VARCHAR  max     0                   0

User · Answer

Do not use floats to store fixed-point  accuracy-required data  This example shows how to convert a float to NVARCHAR 50  properly  while also showing why it is a bad idea to use floats for precision data   create table  f   Column Name  float  insert  f select 9072351234 insert  f select 907235123400000000000  select     cast  Column Name  as nvarchar 50        --cast  Column Name  as int   Arithmetic overflow     --cast  Column Name  as bigint   Arithmetic overflow     CAST LTRIM STR  Column Name  50   AS NVARCHAR 50   from  f   Output  9 07235e 009    9072351234 9 07235e 020    907235123400000010000   You may notice that the 2nd output ends with  10000  even though the data we tried to store in the table ends with  00000    It is because float datatype has a fixed number of significant figures supported  which doesn t extend that far

[sql] cast or convert a float to nvarchar?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to casting