Monday, 5 March 2012

CONVERT float to nvarchar

Attempting to compare company numbers (e.g. ABN) stored as datatype float to text strings is complicated by the fact that the default display for float numbers over 6 digits is scientific notation. To work around this you need firstly to convert the float to a bigint, then convert to nvarchar.

SELECT CONVERT (nvarchar(14),CONVERT(bigint,t.FloatABN))
FROM Table t

This conversion then allows a join to be made between the text ABN and the float ABN.

FROM Table t
INNER JOIN Table2 t2
ON t.TextABN = CONVERT (nvarchar(14),CONVERT(bigint,t.FloatABN))

1 comment:

  1. what is scientific notation
    Scientific Notation include in the mathematics course. In the world of science some time we deal with numbers which are very small and those which are very large. In some branches of science large numbers while in others very small numbers are used.