Converting FLOAT to VARCHAR

The conversion from a float datatype to varchar datatype can be fraught with peril!
Lets take our fictional number 7555000001 which is helpfully stored in a float datatype. Now in the context of a telephone number we have an issue. When we convert to a varchar directly using
SELECT CONVERT(VARCHAR(22), CONVERT(FLOAT,‘7555000001’) we rather unhelpfully get 7.555e+009 as the output.
So we need to retain precision before the conversion to varchar with SELECT CONVERT(VARCHAR(22), CAST(CONVERT(FLOAT,‘7555000001’) AS DECIMAL(38,0))) which now gives us our desires output 7555000001, but stored in a varchar field and we can then prefix a zero or the country code.
Using this against our table we might update with the below
CONVERT(VARCHAR(22), CAST([Telephone] AS DECIMAL(38,0)))
Converting FLOAT to VARCHAR

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest