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?

Stigmasterol

Even though a phone number is a type of 'number', a general rule of thumb is to ONLY store things as numbers if you are planning on doing some type of calculation with them. If the number you are intending on storing is never going to need any manipulation(with the exception of an update or something along those lines), you should store them as a string of some sort. – Jaret

Answer 1

Check STR. You need something like SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs.

Gunwale

If it helped, I would expect "+"/best answer from you :) – Gunwale

NOTE: STR causes leading spaces. Ex: SELECT 'A' + STR(123.0,10,0) + 'B' gives the string "A 123B". Must use LTRIM or CONVERT to decimal first. – Carliecarlile

This gives me asterisks instead of digits – Istle

Answer 2

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...

Syllabi

True enough, but you can always use bigint instead if you have phone numbers greater than 4 billion. In any case, the STR function looks like a better option that I didn't know about before this question! – Syllabi

In North America, phone numbers never start with 0. But rounding error would still be a problem! – Monarchism

That's the only solution that worked, you can use bigint if the numbers too big. – Istle

Answer 3

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.

Prothonotary

Answer 4

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.

Tempest

this worked well for me. didn't add any leading or trailing spaces as well. – Vampirism

Answer 5

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

Alas

This will give 2 digits after the decimal point with zeros in case you have an integer which is not an expected result. – Istle

Answer 6

You can also do something:

SELECT CAST(CAST(34512367.392 AS decimal(30,9)) AS NVARCHAR(100))

Output: 34512367.392000000

Hitherward

Answer 7

I had same problem and i saw your solution. Good solution, its worked, thank you... I created a function with your codes. Now i use it. My function is here:

create function dbo.fnc_BigNumbertoNvarchar (@MyFloat float)

returns NVARCHAR(50)

AS

BEGIN
    RETURN REPLACE (RTRIM (REPLACE (REPLACE (RTRIM ((REPLACE (CAST (CAST (@MyFloat AS DECIMAL (38 ,18 )) AS VARCHAR( max)), '0' , ' '))), ' ' , '0'), '.', ' ')), ' ','.') 
END

Priapus

Answer 8

Continuing a1ex07's answer - to use STR function (SQL SERVER),
and Ronen Festinger's comment - that he gets asterisks instead of digits,
I wanted to point out that the default length of STR is 10,
therefore, for large numbers, don't forget to use the length argument For example: STR(1234567890123, 14)

Auscultate

When you add details to an existing answer you might consider writing a comment instead of a new answer. Everybody who is just reading the other answer might miss your input. – Bananas

I wanted to do so, though I don't have enough reputation (50) to comment, but I was glad to find out I can comment on my own answer – Auscultate

Sorry, I wasn't aware of this. Welcome to SO, keep contributing! – Bananas

Answer 9

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'), '.', ' ')), ' ','.')

Catechumen

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – Lavenialaver