Menu
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?
849
CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data's format. What is this?
Summary. Casting and converting are ways in which we can change a value from one type to another; casting is faster but more prone to errors, while conversion is more computationally expensive but also more forgiving.
NVARCHAR has higher precedence than the VARCHAR data type. Therefore, during the data type conversion, SQL Server converts the existing VARCHAR values into NVARCHAR.
Cast() Function in SQL ServerThe Cast() function is used to convert a data type variable or data from one data type to another data type. The Cast() function provides a data type to a dynamic parameter (?) or a NULL value. The data type to which you are casting an expression is the target type.
Here we will see, how to convert FLOAT data to NVARCHAR data in an MS SQL Server’s database table using the CAST (), CONVERT (), and FORMAT () functions. We will be creating a person table in a database called “geeks”. We have the following Employee table in our geeks database :
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.
Must use LTRIM or CONVERT to decimal first. 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. In these examples the innermost cast (1234567890 as float) is used in place of selecting a value from the appropriate column.
I needed to convert the float field to varchar as I need to display NA when NULL and 0 as it is. I achieved this by adding CASE statement in the query as below; CASE WHEN float_field IS NULL THEN 'NA' WHEN float_field = 0 THEN '0' ELSE CONVERT (VARCHAR, float_field, 128) END AS float_As_VChar
Check STR. You need something like SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs.
158
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...
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
