Data quality in SQL Server // TRY_CONVERT for float and real done safely

If you have ever imported a series of measurements where every empty cell landed in the target table as 0, you know the trap: TRY_CONVERT(float, '') does not return NULL, it returns 0. The average across that column is wrong afterwards, and nothing about the result gives it away.

At a glance:

• real (= float(24), binary32, 4 bytes) and float (= float(53), binary64, 8 bytes) are approximate types. They store values in binary rather than decimal, which is why 2 + 3.4 - 3.4 - 2 as a float does not come out as 0 but leaves a remainder of roughly 4.44E-16.
• When converting text to float, the period acts as the decimal separator. A comma yields NULL, regardless of any locale setting. Other target types behave differently — money, for instance, swallows commas.
• The empty string is the dangerous edge case: it becomes 0 rather than NULL. A CASE/TRIM step ahead of the conversion produces the semantically correct empty value.
• Postgres maps the same types to double precision and real, both following IEEE 754 and therefore with the same rounding behaviour. On invalid text, however, Postgres aborts with an exception. The NULL behaviour comes from a PL/pgSQL wrapper fn_try_cast_double.

Prerequisite: TRY_CONVERT has existed since SQL Server 2012. The safe pattern below uses TRIM and therefore needs SQL Server 2017 or newer, before that LTRIM(RTRIM(…)). The Postgres examples do not require a specific version, with one exception: pg_input_is_valid arrived with Postgres 16. All examples run without a sample database.

Content

• Float and real — two approximate types
• Precision and storage
• Converting text to float
• Converting typed numbers to float
• Decimal vs. Float
• Safe type conversion
• Postgres bridge
• Summary
• FAQ
• Related Posts

Float and real — two approximate types

SQL Server provides two data types for storing floating-point numbers: float(n) and real. Neither is a precise data type. They store a value as a binary mantissa with an exponent. That makes large value ranges representable with little storage, at the expense of accuracy. A float variable may appear to hold the value 0 while still carrying a non-zero remainder in a less significant digit. Typical uses are technical measurements, sensor data and scientific calculations, in other words anywhere an accuracy of 7 to 15 significant digits is enough.

Microsoft Learn on TRY_CONVERT and on CAST and CONVERT states that the value to be converted may be any expression. That makes both nvarchar strings from CSV, JSON and XML imports valid inputs and already typed numbers as well, say a decimal(18, 5) from a pipeline calculation. The two cases behave differently enough to be worth treating separately. The safe conversion pattern follows after that.

Precision and storage

Type choice comes before conversion. The two floating-point types differ in the number of bits reserved for the mantissa, and therefore in storage size and accuracy:

The parameter n in float(n) accepts values from 1 to 53, but internally there are only two tiers. For n from 1 to 24, SQL Server reserves 4 bytes and stores the value in single precision, exactly like real. For n from 25 to 53 it is 8 bytes in double precision, exactly like float without a parameter. If n is omitted, SQL Server assumes 53.

In concrete terms: SQL Server normalises n to one of the two tiers. Values from 1 to 24 end up as float(24) and therefore on the same representation as real, values from 25 to 53 as float(53). A declaration such as float(30) does not create a size of its own.

This article works mostly with float without a parameter, that is with float(53). The differences against real concern precision and storage only, not the conversion rules.

Converting text to float

When a value of type nvarchar or varchar is passed to TRY_CONVERT, the text has to represent a number. On this path from text to float, SQL Server expects the period as decimal separator and understands neither a comma nor a thousands separator. That is a property of this conversion path, not of TRY_CONVERT as a function — with money and smallmoney, commas are even swallowed silently. Scientific notation, on the other hand, is allowed. The empty string plays a special role that the end of this section comes back to.

  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
 10: SELECT TRY_CONVERT(float, NCHAR(9)    )  -- NULL
 11: SELECT TRY_CONVERT(float, N'1e400'    )  -- NULL
 12: SELECT TRY_CONVERT(float, N'Infinity' )  -- NULL

  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
 10: SELECT TRY_CONVERT(float, NCHAR(9)    )  -- NULL
 11: SELECT TRY_CONVERT(float, N'1e400'    )  -- NULL
 12: SELECT TRY_CONVERT(float, N'Infinity' )  -- NULL

Line by line:

• Line 3: '123,456' with a comma yields NULL. The comma is not recognised as a decimal separator, and neither SET LANGUAGE nor SET DATEFORMAT changes that. Anyone importing from a CSV in German notation has to normalise before the call.
• Lines 5, 6: the empty string and a string of nothing but spaces both become 0. With decimal and numeric the same input would return NULL.
• Lines 7, 8: leading and trailing spaces do not prevent the conversion.
• Line 9: scientific notation already works as text. When converting text directly to decimal, it is not permitted: TRY_CONVERT(decimal(5, 2), N'123456E-3') returns NULL. Passed as an already typed number, decimal handles it just fine.
• Line 10: a lone tab character yields NULL, not 0. The 0 applies to actual spaces only. A non-breaking space behaves like the tab.
• Lines 11, 12: a value beyond the float range yields NULL, and SQL Server does not know the spellings 'Infinity' and 'NaN'. Postgres accepts both, see Postgres bridge.

Key point: Of the two surprises, the second is the more dangerous one. A NULL from a comma input stands out in the target table and ends up in error handling. The 0 from an empty string does not stand out. It is a valid measurement, it survives every NOT NULL check, and it shifts every average across the column. An empty field in a CSV means “unknown”, not “zero units”.

Converting typed numbers to float

When the input is already typed, as an integer, a decimal number or in scientific notation, the number of cases drops considerably:

  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

  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

Line by line:

• Line 3: what looks like a decimal separator here is the T-SQL argument separator. TRY_CONVERT is defined as a three-argument function (data_type, expression, style), so the parser reads the 456 as style, and for float that parameter has no effect. What comes back is the first expression, 123 — a plausible-looking value without its decimal places. On the text path the same comma would have failed visibly with a NULL.
• Line 4: the decimal literal 123.456 is not a floating-point number at all. T-SQL types it as numeric(6, 3), and only the conversion turns it into a float value.
• Line 5: a literal with an E exponent, by contrast, is a float literal from the outset. The conversion takes the value unchanged.

Decimal vs. Float

Unlike float and real, decimal is a precise data type. It stores values as a signed sequence of decimal digits and represents them exactly within the declared precision and scale (decimal(precision, scale)). A calculation such as 2 + 3.4 - 3.4 - 2 therefore reliably returns 0. float, on the other hand, stores binary with a fixed mantissa length. Many decimal numbers that look tidy in human notation (0.1, 0.2, 3.4) cannot be represented exactly in that binary form. Operations therefore accumulate rounding errors on the order of the mantissa resolution.

The same calculation once as float, once as decimal:

  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: 
  7: DECLARE @d1 AS decimal(2, 1) = 2;
  8: DECLARE @d2 AS decimal(2, 1) = 3.4;
  9: DECLARE @d3 AS decimal(2, 1) = @d1 + @d2;
 10: 
 11: SELECT @d3 - @d2 - @d1                  -- 0.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: 
  7: DECLARE @d1 AS decimal(2, 1) = 2;
  8: DECLARE @d2 AS decimal(2, 1) = 3.4;
  9: DECLARE @d3 AS decimal(2, 1) = @d1 + @d2;
 10: 
 11: SELECT @d3 - @d2 - @d1                  -- 0.0

On the float path, a remainder of roughly 4.44E-16 is left after the subtraction, a typical rounding error within the 53-bit mantissa of float in the binary64 format defined by IEEE 754. The value is not the resolution limit of the data type but the result of the three operations involved. The same computation on the same server reproducibly returns exactly this remainder. On the decimal path the result is exactly 0.0, because no binary approximation takes place.

The same calculation with real returns exactly 0. The representation error is in fact larger there, since 3.4 lands on 3.4000000953674316 as a real and on 3.3999999999999999 as a float. In this particular sequence of additions and subtractions it cancels out completely. Anyone checking only this one calculation would wrongly conclude that real is the more accurate type. Rounding errors are a property of the computation path, not a constant of the data type.

There is also the question of when this decision is made. Casting later repairs nothing, because the float value already carries the approximation:

  1: SELECT CAST(CAST(3.4 AS float) AS decimal(20, 17))   -- 3.39999999999999991
  2: SELECT CAST(3.4 AS decimal(20, 17))                  -- 3.40000000000000000

  1: SELECT CAST(CAST(3.4 AS float) AS decimal(20, 17))   -- 3.39999999999999991
  2: SELECT CAST(3.4 AS decimal(20, 17))                  -- 3.40000000000000000

Line 1 merely produces a decimal spelling of an already approximated value. The deviation in the last digit cannot be computed away any more.

Values that have to be represented exactly therefore belong in a decimal(p, s) column right at import time: monetary amounts, regulated reporting figures and decimally defined quantities such as weights or volumes. Plain counts belong in an integer type instead. Anyone who stores such values as float first and casts later has already let the information loss happen. float and real belong to values that come with a measurement tolerance anyway. Conversely, decimal is not unconditionally exact either, because converting to a smaller scale rounds silently and arithmetic operations follow their own precision rules. Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely covers both.

Safe type conversion

“Safe” in this series means fault-tolerant: no abort, NULL instead of a runtime error. It does not automatically mean lossless. With float a second limitation applies, because the target type is an approximation and the result therefore inexact from the start.

The sections above showed two edge cases that have to be handled explicitly in the import path:

• Empty string: it converts to 0. Where that is the wrong semantics, and with CSV imports it usually is, it has to be mapped to NULL before the TRY_CONVERT.
• Comma as decimal separator: it leads to NULL. If the source delivers German notation ('123,456'), the comma has to be replaced with a period before the TRY_CONVERT.

The following example handles both edge cases together:

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

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

Four limitations come with this pattern:

• Known source notation: REPLACE(',', '.') is only correct if it is established that the comma is the decimal separator for this source. Taken on its own, '1,234' is ambiguous — 1.234 in German reading, 1234 in American, a factor of 1000 apart, and both paths return a value without complaint. With mixed or unknown notations, the notation has to be determined first, or the record rejected as a data quality error, before anything is replaced.
• Input grammar: the pattern normalises the decimal separator only. A thousands separator stays put, which turns '1.234,56' into '1.234.56' and ultimately into NULL. Such inputs need a cleanup step upstream. Whitespace, on the other hand, is uncritical here: without further arguments TRIM removes ordinary spaces only, and those are exactly the case that would otherwise become 0. Tabs and non-breaking spaces convert to NULL anyway.
• Value range: a value beyond 1.79E+308 yields NULL, and with real that boundary already starts at 3.4E+38. A NULL in the target field therefore has three possible causes: an invalid number representation, a value outside the range, or a genuine NULL in the source. For error diagnosis it pays to keep the raw value.
• Precision limit: float(53) has a binary precision of 53 bits, which corresponds to roughly 15 significant decimal digits. The occasionally quoted 17 digits mean something else, namely how many decimal places are needed to write a binary64 value as text and read it back without loss. For the conversion, the first number is what counts. The pattern makes it fault-tolerant, but not exact, and where exactness matters, decimal(p, s) is the right target type.

If you need the pattern for several columns in an ETL process, abstract it into a user-defined function fn_try_convert_float(@p_input nvarchar). Design Pattern // Safe type conversion with T-SQL provides the blueprint.

Postgres bridge

For floating-point numbers, switching between the engines is less critical than for any other type in this series. Both map float(53) and double precision respectively onto the IEEE 754 binary64 format, Postgres on all currently supported platforms according to its documentation. The same finite value therefore sits identically in memory in both, and the comparison calculation from the section above produces exactly the same remainder in Postgres. It is still not a general portability guarantee: parsing, intermediate results, optimisation and output formatting remain a matter for each engine.

There are three differences in conversion behaviour:

• No try counterpart: CAST(s AS double precision) is the Postgres way, and it throws an exception on invalid text. There is still no direct equivalent to TRY_CONVERT, neither as a built-in try_cast function nor as a CAST … ON ERROR syntax, last checked against Postgres 18. So the NULL behaviour is something you write yourself.
• Empty string: Postgres aborts where SQL Server converts to 0. ''::double precision reports invalid input syntax with SQLSTATE 22P02. That is the friendlier variant, because the error becomes visible instead of disguising itself as a measurement.
• Infinity and NaN: Postgres accepts both as valid inputs and stores them as values. SQL Server rejects them with NULL. Anyone moving measurement series between the engines is better off catching these two spellings before the transfer.

The wrapper does not reproduce TRY_CONVERT alone but the whole safe pattern from the section above: empty string to NULL, comma to period, error to NULL.

  1: CREATE OR REPLACE FUNCTION fn_try_cast_double
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS double precision
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10: 
 11:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 12:       RETURN NULL;
 13:    END IF;
 14: 
 15:    RETURN CAST(REPLACE(p_input, ',', '.') AS double precision);
 16: 
 17: EXCEPTION
 18:    WHEN invalid_text_representation OR numeric_value_out_of_range THEN
 19:       RETURN NULL;
 20: END;
 21: $function$;
 22: 
 23: SELECT fn_try_cast_double('123.456');     -- 123.456
 24: SELECT fn_try_cast_double('123,456');     -- 123.456
 25: SELECT fn_try_cast_double('123456E-3');   -- 123.456
 26: SELECT fn_try_cast_double('');            -- NULL
 27: SELECT fn_try_cast_double('1e400');       -- NULL

  1: CREATE OR REPLACE FUNCTION fn_try_cast_double
  2: (
  3:     IN    p_input              text
  4: )
  5: RETURNS double precision
  6: LANGUAGE plpgsql
  7: IMMUTABLE
  8: AS $function$
  9: BEGIN
 10: 
 11:    IF p_input IS NULL OR TRIM(p_input) = '' THEN
 12:       RETURN NULL;
 13:    END IF;
 14: 
 15:    RETURN CAST(REPLACE(p_input, ',', '.') AS double precision);
 16: 
 17: EXCEPTION
 18:    WHEN invalid_text_representation OR numeric_value_out_of_range THEN
 19:       RETURN NULL;
 20: END;
 21: $function$;
 22: 
 23: SELECT fn_try_cast_double('123.456');     -- 123.456
 24: SELECT fn_try_cast_double('123,456');     -- 123.456
 25: SELECT fn_try_cast_double('123456E-3');   -- 123.456
 26: SELECT fn_try_cast_double('');            -- NULL
 27: SELECT fn_try_cast_double('1e400');       -- NULL

The EXCEPTION block deliberately catches the two expected error classes, the invalid number representation (invalid_text_representation, SQLSTATE 22P02) and the range overflow (numeric_value_out_of_range, SQLSTATE 22003). A blanket WHEN OTHERS would silently turn unexpected errors into NULL as well. For real the wrapper looks identical, only with RETURNS real.

According to the Postgres documentation, however, a block with an EXCEPTION part is significantly more expensive than one without. Since Postgres 16 there has been a set-based alternative: pg_input_is_valid checks an input against a target type, and pg_input_error_info supplies the message and SQLSTATE. Both check type validity only, so normalisation with TRIM and REPLACE stays upstream.

  1: SELECT pg_input_is_valid('123.456' , 'double precision');   -- true
  2: SELECT pg_input_is_valid('123,456' , 'double precision');   -- false
  3: SELECT pg_input_is_valid('1e400'   , 'double precision');   -- false
  4: SELECT pg_input_is_valid('Infinity', 'double precision');   -- true
  5: 
  6: SELECT
  7:     sql_error_code
  8:    ,message
  9: FROM
 10:    pg_input_error_info('1e400', 'double precision');
 11: -- 22003 | "1e400" is out of range for type double precision

  1: SELECT pg_input_is_valid('123.456' , 'double precision');   -- true
  2: SELECT pg_input_is_valid('123,456' , 'double precision');   -- false
  3: SELECT pg_input_is_valid('1e400'   , 'double precision');   -- false
  4: SELECT pg_input_is_valid('Infinity', 'double precision');   -- true
  5: 
  6: SELECT
  7:     sql_error_code
  8:    ,message
  9: FROM
 10:    pg_input_error_info('1e400', 'double precision');
 11: -- 22003 | "1e400" is out of range for type double precision

When the input delivers formatted numbers with thousands and decimal separators, to_number with a format pattern is the tool of choice. The call is bound to the locale via the session variable lc_numeric:

  1: SET lc_numeric = 'de_DE.utf8';
  2: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1234.56
  3: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1.23
  4: 
  5: SET lc_numeric = 'en_US.utf8';
  6: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1.23
  7: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1234.56
  8: 
  9: -- Literal format characters are unaffected by the locale:
 10: SELECT to_number('1,234.56', 'FM999,999.99');   -- 1234.56 under both locales

  1: SET lc_numeric = 'de_DE.utf8';
  2: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1234.56
  3: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1.23
  4: 
  5: SET lc_numeric = 'en_US.utf8';
  6: SELECT to_number('1.234,56', 'FM999G999D99');   -- 1.23
  7: SELECT to_number('1,234.56', 'FM999G999D99');   -- 1234.56
  8: 
  9: -- Literal format characters are unaffected by the locale:
 10: SELECT to_number('1,234.56', 'FM999,999.99');   -- 1234.56 under both locales

More important than the locale is what to_number does not do: characters that are not provided for in the format pattern are silently skipped by the parser.

  1: SELECT to_number('1.234,56abc' , 'FM999G999D99');   -- 1.23
  2: SELECT to_number('EUR 1.234,56', 'FM999G999D99');   -- 1.23
  3: SELECT to_number('12ab34'      , 'FM999G999D99');   -- 1234

  1: SELECT to_number('1.234,56abc' , 'FM999G999D99');   -- 1.23
  2: SELECT to_number('EUR 1.234,56', 'FM999G999D99');   -- 1.23
  3: SELECT to_number('12ab34'      , 'FM999G999D99');   -- 1234

Line 3 is the extreme case: an obviously broken input turns into a plausible number. to_number is a formatting parser, not a validator. For a data quality check, the input belongs to be validated against the expected format first, for instance with pg_input_is_valid.

Key point: In both cases to_number reports no error and returns a silently wrong value — with a mismatched locale just as with a broken input (both verified against PostgreSQL 17). This is why the input format belongs written down explicitly in an ETL process: G and D pull the separator from lc_numeric, whereas a . or , in the format string is a literal character and locale-independent, as line 10 above shows. If you know the notation of the source, that is how you pin it down. One detail for the way back: to_number returns numeric, so double precision needs a closing cast.

Type mapping T-SQL to Postgres:

Summary

• real (= float(24), binary32, 4 bytes) and float (= float(53), binary64, 8 bytes) are approximate types, and the parameter n knows only these two tiers.
• From text, a comma yields NULL, an empty string yields 0, and scientific notation yields the expected value. Only the 0 arrives without a warning signal, so it is the one that needs the CASE/TRIM step.
• Written as a literal in source code, 123, 456 becomes the style parameter and returns 123. No pattern catches that, only code discipline does.
• Rounding errors are a property of the computation path. If you need exact decimal values, decide that when designing the schema, because casting stored float values later does not bring the approximation back.
• Postgres uses the same IEEE 754 format and stores the same finite value identically, but aborts on invalid text. For that there is the wrapper fn_try_cast_double, the pre-check pg_input_is_valid from Postgres 16 onwards, and to_number with an explicitly pinned format for formatted numbers.

A decision aid for practice:

FAQ

Related Posts

ETL context:

• Data quality in an ETL process
• ETL vs. ELT — How to Tell Which Pattern You Actually Built — the macro view: ETL vs. ELT as an architecture decision, not the order of the letters.

TRY_CONVERT for other data types:

• Data quality in SQL Server // TRY_CONVERT for date, datetime, datetime2 and time done safely
• Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely
• Data quality in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely
• Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely
• Data quality in SQL Server // TRY_CONVERT for bit done safely — converting yes/no values

Fundamentals:

• Data Quality // Type Conversion Basics with T-SQL — CAST, CONVERT, TRY_CAST and TRY_CONVERT compared.