Using TRY_CONVERT in SQL Server for Safer Casting
By Tom Nonmacher
SQL Server is a highly robust and versatile database management system, employed by businesses of all sizes across the globe. One of the functionalities that sets it apart is its ability to handle data conversion errors more gracefully. In this blog post, we will be focusing on the TRY_CONVERT function introduced in SQL Server 2012 and still present in SQL Server 2016 and SQL Server 2017, providing a safer way to cast and convert data types.
Casting in SQL Server refers to the process of converting a data type of a column into another data type. The TRY_CONVERT function attempts this conversion and returns a value if it is successful. If the conversion fails, instead of raising an error, it returns NULL. This makes the code more resilient, as it can handle unexpected input formats without breaking.
Consider the following example, where we attempt to convert a string into an integer:
-- T-SQL code
SELECT TRY_CONVERT(INT, '123') AS Result;
-- Result is 123
SELECT TRY_CONVERT(INT, 'ABC') AS Result;
-- Result is NULL
In the first case, the conversion is successful and 123 is returned. In the second case, 'ABC' cannot be converted to an integer, so instead of raising an error, NULL is returned.
This feature is not limited to SQL Server. Other database systems like MySQL 5.7 and DB2 11.1 also have similar features. In MySQL, the CAST function can be used in combination with the IFNULL function to achieve similar results:
-- MySQL code
SELECT IFNULL(CAST('123' AS UNSIGNED), 0) as Result;
-- Result is 123
SELECT IFNULL(CAST('ABC' AS UNSIGNED), 0) as Result;
-- Result is 0
In DB2 11.1, the CAST function can be used in combination with the NULLIF function:
-- DB2 code
SELECT NULLIF(CAST('123' AS INT), '') as Result FROM SYSIBM.SYSDUMMY1;
-- Result is 123
SELECT NULLIF(CAST('ABC' AS INT), '') as Result FROM SYSIBM.SYSDUMMY1;
-- Result is NULL
In conclusion, the TRY_CONVERT function in SQL Server and its equivalents in other systems like MySQL and DB2 offer a more resilient way of handling data type conversions. Whether you are using Azure SQL, SQL Server 2016, SQL Server 2017, MySQL 5.7, or DB2 11.1, these functions allow for more robust and error-resistant code by handling conversion errors gracefully, and returning NULL instead of raising an error. This is a must-have tool in every SQL programmer's toolbox, and one that can significantly improve the robustness of your code.