I am a little behind the curve on this one. Starting in SQL Server 2012, Microsoft introduced the TRY_CONVERT() function. This function works the same way as the CONVERT() function, but if the convert doesn’t work, you get NULL instead of an error.
Let’s take a look at the old way:
SELECT CONVERT(INT, 'aaa')
This returns the message “Conversion failed when converting the varchar value ‘aaa’ to data type int.” If you are doing a CONVERT() on some column in a large dataset, this often can throw you while you try to sort out what went wrong. Enter TRY_CONVERT(). Here is the new syntax:
SELECT TRY_CONVERT(INT, 'aaa')
This just returns NULL and doesn’t error at all. In this case, you can easily provide a default value for the conversion by using ISNULL() or COALESCE()
SELECT ISNULL(TRY_CONVERT(INT, 'aaa'), 0) -- OR SELECT COALESCE(TRY_CONVERT(INT, 'aaa'), 0)
Pretty darn awesome and about time (well, even four years ago, it was about time!).
T-SQL IIF on December 20th, 2016
[…] T-SQL TRY_CONVERT() […]