SQL Server converts a numeric value to a string

When I import some data to SQL Server today, a column is imported into the float type, and what I actually need is the varchar type, so type conversion is required, I encountered some problems during conversion, so I wrote this blog record.

SQL Server has two numeric types: bit, tinyint, smallint, int, bigint, smallmoney, money, and decimal, these data types can accurately indicate a certain value; the other is an approximate value type, specifically float and real. Floating point data is an approximate value, so not all values in the Data Type range can be accurately expressed.

Sometimes we need to convert these numeric types to the string type, and the conversion functions used are cast and convert. These two functions are used for type conversion, but the syntax format is different. It is said that there are still some differences during conversion, but I personally prefer to use the convert function. This function is more like a function syntax on the one hand, on the other hand, you can specify the conversion format when converting time and value into a string.

For the data type of the exact value, the converted string is the value we store. For example:

Declare @ I intset @ I = 123456789
Print 'test: '+ convert (varchar (20), @ I)
The output is: test: 123456789.

However, for data types of approximate values, it is not that simple.

Declare @ I floatset @ I = 123456789
Print 'test: '+ convert (varchar (20), @ I)
Output result: test: 1.23457e + 008

The output result is represented by scientific notation. Can you specify a conversion style to avoid scientific notation? Mentioned in the help documentFloatOrRealWhen converting to character dataStyleValue:

0(Default) the maximum value is 6 digits. Use scientific notation as needed.

1Always 8 bits. Always use scientific notation.

2Always a 16-bit value. Always use scientific notation.

Our value is 123456789, which exceeds 6 digits. Therefore, scientific notation is used for both 0 and 1. So how can we output our data without converting it into scientific notation? A simple method is to convert the approximate data to the exact data, and then convert the exact data to a string.

The preceding example shows how to convert two data types:

Declare @ I floatset @ I = 123456789
Print 'test: '+ convert (varchar (20), convert (int, @ I ))
Output: test: 123456789.

If there are decimal places, we can convert them to decimal places. The number of decimal places is specified when decimal is defined. For example, to output four decimal places, the conversion code is:

Declare @ I floatset @ I = 123456789.12
Print 'test: '+ convert (varchar (20), convert (decimal (18, 4), @ I ))
Output: test: 123456789.1200.