Skip to content

float to string no scientific notation: SQL Server

Tajinder
DataDog

Have a problem with some wonky ID’s that I can’t quite figure out.

-------------------------------------------
|ID1                |    ID2              |
-------------------------------------------
|1.3787547414298E+18|  1378754741429799395|
-------------------------------------------

The above is a result of joining two tables, where ID1 = ID2. ID1 cannot be null, however ID2 can be null.

What I’m attempting to arrive at is:

-------------------------------------------
|ID1                |    ID2              |
-------------------------------------------
|1378754741429799395|  1378754741429799395|
-------------------------------------------

So I can then drop the other column. I’ve tried casting, str function, removing the period in the ingestion process, and I’ve been stuck for about 2 hours.

The data type for ID1 is a float, whereas the data type for ID2 is a nvarchar(which is what I want).

Advertisement

Answer

When you join two tables on ID1 = ID2 and the type of ID1 is float and the type of ID2 is nvarchar, the engine implicitly converts nvarchar into a float, not the other way around.

float type has max precision of 15 digits, so your nvarchar value that has 19 digits is rounded with the loss of precision. After this, both original float value of ID1 and converted float value of ID2 are equal, so your join returns a row.

It is easy to check:

DECLARE @V1 float = 1.3787547414298E+18;
DECLARE @V2 nvarchar(30) = 1378754741429799395;

SELECT @V1, @V2, CAST(@V2 as float);

Result

1.3787547414298E+18    1378754741429799395    1.3787547414298E+18

It is impossible to convert 1.3787547414298E+18 into 1378754741429799395, the float type simply doesn’t have this data, the data has been lost.

You can try

SELECT CAST(@V1 as decimal(38))

and you’ll get

1378754741429799936

If you use another method, you’ll get different result. For example:

SELECT STR(@V1, 30, 0)

returns

1378754741429799900

Many different nvarchar values correspond to the same float value:

DECLARE @V2 nvarchar(30) = 1378754741429799395;
DECLARE @V3 nvarchar(30) = 1378754741429800000;
DECLARE @V4 nvarchar(30) = 1378754741429795000;
DECLARE @V5 nvarchar(30) = 1378754741429799400;
DECLARE @V6 nvarchar(30) = 1378754741429799999;

SELECT
     CAST(@V2 as float)
    ,CAST(@V3 as float)
    ,CAST(@V4 as float)
    ,CAST(@V5 as float)
    ,CAST(@V6 as float)
;

Result

1.3787547414298E+18 1.3787547414298E+18 1.3787547414298E+18 1.3787547414298E+18 1.3787547414298E+18