TRY_CONVERT // Converting data to float, real

marcus

Content

Overview

SQL Server provides the following data types for storing floating point numbers:

  • float(n)
  • real

The data types float and real are not precise data types. They are suitable for storing particularly large or small values, but at the expense of accuracy. This means that a number equal to 0 can apparently be stored, but which has a value other than 0 in a less significant and not displayed position after the decimal point. They are mainly used in technical applications in which measured values and technical data have to be saved.

The data types float and real differ in the precision with which a floating point number is stored. The data type float expects an optional parameter n, which specifies the number of bits used to store the mantissa (number before the exponent). The values 1 to 53 are permitted here. If a number is less than or equal to 24, a float value takes up 4 bytes of storage space. For a number greater than 24, a float value takes up 8 bytes of storage space. The data type real is equivalent to float(24). If precision is not specified, SQL Server assumes the value 53. This article only considers the data float type without specifying the parameter n. A separate section deals with the effect of the precision of the data types decimal and float.

As with the other data types described, a distinction is made here as to whether the value to be converted is passed as text or as a typed number (no text).

Text

If a value of type text is passed in the expression parameter, the value passed must represent a number. A point is always expected as the decimal separator. Thousand separators are not permitted. If the parameter contains a comma, the value cannot be converted and the TRY_CONVERT function returns NULL. Since decimal numbers delivered via a text file sometimes use a comma as a decimal separator, commas should generally be replaced with a period.

Leading and trailing spaces are truncated by the function and do not prevent conversion. An empty string is translated as 0 in contrast to the target data type decimal. Converting a number in scientific notation is supported. The converted value is not rounded. The following code example shows the results of the TRY_CONVERT function for various valid and invalid decimal numbers passed as text of type nvarchar and converted to a number of type float:

 1: SELECT TRY_CONVERT(float, NULL        ) -- > NULL
 2: SELECT TRY_CONVERT(float, N'123'      ) -- > 123
 3: SELECT TRY_CONVERT(float, N'123,456'  ) -- > NULL
 4: SELECT TRY_CONVERT(float, N'123.456'  ) -- > 123.456
 5: SELECT TRY_CONVERT(float, N''         ) -- > 0
 6: SELECT TRY_CONVERT(float, N' '        ) -- > 0
 7: SELECT TRY_CONVERT(float, N'  123.456') -- > 123.456
 8: SELECT TRY_CONVERT(float, N'123.456  ') -- > 123.456
 9: SELECT TRY_CONVERT(float, N'123456E-3') -- > 123.456

Lines 3, 5 and 6 require special attention. The input parameter in line 3 contains a comma as a decimal separator and therefore cannot be converted. If the input value, as noted in lines 5 and 6, is an empty string or a text with only spaces, the TRY_CONVERT function converts it to the value 0. This behavior can be viewed as an error in the TRY_CONVERT function. It should be taken into account that an empty character string or a text with only spaces is be replaced by a NULL before conversion.

No Text

If a number is passed into expression as a typed number rather than text, the function can convert any number to a value of type float. There is no rounding. The following code example shows the results of the TRY_CONVERT function for various valid and invalid numbers passed as typed values, each converted to a number of type float: 

 1: SELECT TRY_CONVERT(float, NULL     ) -- > NULL
 2: SELECT TRY_CONVERT(float, 123      ) -- > 123
 3: SELECT TRY_CONVERT(float, 123,456  ) -- > 123
 4: SELECT TRY_CONVERT(float, 123.456  ) -- > 123.456
 5: SELECT TRY_CONVERT(float, 123456E-3) -- > 123.456

It should be noted here that the number in line 3 is not a decimal number. The value after the comma is interpreted as a style parameter, which, however, has no effect on the type conversion.

decimal vs. float

In contrast to the data types float and real, decimal is a precise data type in which the number of decimal places can be specified and the decimal places are stored exactly.

Even simple calculations such as adding and subtracting float values can produce an undesirable result. In the following code example, the result of lines 5 and 12 is expected to be 0. While the result is exact when using variables of typ decimal, when using the data type float we only get an approximation to the value 0.

 1: DECLARE @f1 AS float = 2;               -- 
 2: DECLARE @f2 AS float = 3.4;             -- 
 3: DECLARE @f3 AS float = @f1 + @f2;       -- 
 4:
 5: SELECT @f3 - @f2 - @f1                  -- > 4.44089209850063E-16
 6:                                         -- > 0,0000000000000004440892...
 7:
 8: DECLARE @d1 AS decimal(2,1) = 2;        --
 9: DECLARE @d2 AS decimal(2,1) = 3.4;      -- 
10: DECLARE @d3 AS decimal(2,1) = @d1 + @d2;-- 
11:
12: SELECT @d3 - @d2 - @d1                  -- > 0.0

Save type conversion

Taking into account the above statements, in particular the incorrect behavior of the TRY_CONVERT function when converting an empty string or a text with only spaces, the conversion of an input value to the float data type can be carried out by the following statement:

 1: TRY_CONVERT( [float(n)|real]
 2:             ,REPLACE( CASE WHEN TRIM([Input]) = ''
 3:                          THEN NULL 
 4:                          ELSE [Input] 
 5:                       END
 6:                      , ','
 7:                      , '.'
 8:                     )
 9:            ) AS [Output]

And here is an example …

 1: DECLARE @input as nvarchar(30);
 2: SET @input = '  123.45678';
 3: SELECT TRY_CONVERT(
 4:            float
 5:           ,REPLACE( CASE WHEN TRIM(@input) = ''
 6:                        THEN NULL 
 7                         ELSE @input
 8:                     END
 9:                    , ','
10:                    , '.'
11:                   )
12:            ) AS [Output]

Summary

When converting a value to the float data type, there are two special cases to consider: empty strings are converted to the number 0, thousands separators are not allowed, and only the decimal point is allowed as a decimal separator. The safe conversion of a value to the float data type shown above takes these special cases into account.

Although the difference between the decimal and float data types described above plays a minor role when converting a value in an ETL process, it must be taken into account and, if necessary, in calculations that are carried out in an ETL process with float values decimal data type is preferable to float data type.

Related Posts