# 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

- Data quality in an ETL process
- Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL
- TRY_CONVERT // Converting data to bigint, int, smallint, tinyint
- TRY_CONVERT // Converting data to decimal or numeric
- TRY_CONVERT // Converting data to money, smallmoney
- TRY_CONVERT // Converting data to float, real
- TRY_CONVERT // Converting data to bit
- TRY_CONVERT // Converting data to date, datetime, datetime2, time