TRY_CONVERT // Converting data to decimal, numeric

marcus

This article describes how to safely convert input values to bigint, int, smallint and tinyint data types. A seemingly simple task, you might think. For example, what happens if you want to convert a decimal number or an empty string to a value of type int. Is the converted value automatically rounded? Does function return a NULL on conversion?

The online Microsoft online documentation of the TRY_CONVERT function (or rather CAST and CONVERT) states that the value to be converted can be any expression. This means that function does accept values of any data type: text and non text data types.

When examining how to do safe type conversion, this article only considers meaningful data types that we would actually convert to a bigint, int, smallint, or tinyint value. In addition to whole numbers and decimal numbers, these also include texts, that represent either whole numbers or decimal numbers. The latter is always the case when, for example, values from a text file (CSV, JSON or XML) need to be converted.

For convenience, the article distinguishes between the Text and Non-Text data types and examines the behavior of the TRY_CONVERT function when converting values of the two common data types. For the general data type No Text, only data types are taken into account that we would also convert to a value of type int if necessary. An expression for safe type conversion is presented for both general data types.

Content

Text

If we want to convert a text that is supposed to represent a number to a value of type int, the following scenarios should be examined, among others:

  • The text contains a valid number
  • The number contains a comma as a decimal separator
  • The text contains thousands separators and, if necessary, decimal separators
  • The text is an empty string or contains only spaces

The following shows calls to the TRY_CONVERT function that convert values as described above to a value of type int. The returned result is noted after the respective function calls:

 1: SELECT TRY_CONVERT(int, NULL    ) -- > NULL
 2: SELECT TRY_CONVERT(int, N'123'  ) -- > 123
 3: SELECT TRY_CONVERT(int, N'123,4') -- > NULL
 4: SELECT TRY_CONVERT(int, N'1,234') -- > NULL
 5: SELECT TRY_CONVERT(int, N'123.4') -- > NULL
 6: SELECT TRY_CONVERT(int, N'1.234') -- > NULL
 7: SELECT TRY_CONVERT(int, N''     ) -- > 0
 8: SELECT TRY_CONVERT(int, N' '    ) -- > 0
 9: SELECT TRY_CONVERT(int, N' 123' ) -- > 123
10: SELECT TRY_CONVERT(int, N'123 ' ) -- > 123

Numbers that obviously represent an integer can be converted to a number of type int, as expected. Leading and trailing spaces do not affect the result. Decimal numbers that contain a decimal separator cannot be converted to a number of type int. It is irrelevant whether the value contains a comma (German notation) or a period as a decimal separator (American notation). Empty strings or strings containing only spaces are converted to the value 0.

The result already has a few surprises in store. Decimal numbers cannot be converted and empty strings result in the value 0. While the first result is understandable, the second result is simply wrong. A NULL cannot be delivered in a text file. Whether an empty string should be interpreted as NULL is a question of the data source specification. Interpreting an empty string as 0 is technically incorrect.

No Text

If we want to convert values that are not passed as text to the TRY_CONVERT function, all we need to examine are the following scenarios:

  • The value passed is an integer
  • The value passed is a decimal separator

The following shows calls to the TRY_CONVERT function that convert values as described above to a value of type int. The returned result is noted after the respective instructions:

 1: SELECT TRY_CONVERT(int, 2147483648)    -- > NULL
 2: SELECT TRY_CONVERT(int, 123       )    -- > 123
 3: SELECT TRY_CONVERT(int, 1234.5    )    -- > 123

The first number is greater than the largest positive number of type int. The result therefore returns a NULL as expected. Unlike above, decimals can be converted to a value of type int. However, SQL Server does not perform rounding. The TRY_CONVERT function only returns the integer part of the decimal number

Safe type conversion

The two code examples have shown that at least the conversion of empty strings requires special consideration when conversion to the value 0 is not allowed. Converting a typed decimal number can also give rise to explicit treatment when rounding is required. Alternatively, a conversion to a decimal number with 0 decimal places may be the correct procedure here.

The following code block shows safe type conversion considering converting empty strings to a NULL.

 1: TRY_CONVERT( [bigint|int|smallint|tinyint]
 2:             ,CASE WHEN TRIM([Input]) = ''
 3:                 THEN NULL
 4:                 ELSE [Input]
 5:              END
 6:            ) AS [Output]

And here is an example of the application…

 1: DECLARE @input AS nvarchar(30);
 2: SET @input = '123';
 3: SELECT TRY_CONVERT(int, 
                       CASE WHEN TRIM(@input) = '' THEN NULL ELSE @input END
                      ) AS [Output]

This example converts an empty string to a NULL.

Summary

When converting a text into a value of type int, it must be clarified which data is to be supplied and how special cases are to be taken into account. An empty string is converted to a 0 by the TRY_CONVERT function. Decimal numbers that are available as text are converted to the integer part of the decimal number. If rounding is desired, this must be taken into account during the conversion.

Typed decimal numbers are not rounded when converted to a value of type int. Here, too, it must be clarified whether this result is permissible or whether this feature must be taken into account during the conversion.

Related Posts

.