The output will be an Error with zero rows and the query fails!
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘L00’ to data type int.
We can make use of TRY_PARSE here and the output will come without error and show NULL for the row which had invalid data. The image below shows the same!
SELECT PkId, TRY_PARSE(DirtyColumn AS INT) FROM dbo.DirtyTable
Needless to say, I can always filter the dirty values with a query!
SELECT PkId, TRY_PARSE(DirtyColumn AS INT) FROM dbo.DirtyTable WHERE TRY_PARSE(DirtyColumn AS INT) IS NULL
Another important application of this can be in JOINs! In tables which do not have physical FK relationship established and which has a column that stores both lookup and custom free text values (one of the very common practices in DB design where we need to represent a combo control value in database) this can be handy! Assuming that our DirtyColumn of dbo.DirtyTable has relationship to Production.Product table of AdventureWorks database, running the following query will raise an error:
SELECT PkId, Name, ProductNumber, DirtyColumn FROM Production.Product INNER JOIN dbo.DirtyTable ON ProductID = DirtyColumn
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘L00’ to data type int.
So what we used to do is, explicitly convert the ProductID to VarChar – this was performance sensitive! something like:
SELECT PkId, Name, ProductNumber, DirtyColumn FROM Production.Product INNER JOIN dbo.DirtyTable ON Convert(Varchar, ProductID) = DirtyColumn
Now, we can write the join as:
SELECT PkId, Name, ProductNumber, DirtyColumn FROM Production.Product INNER JOIN dbo.DirtyTable ON ProductID = TRY_PARSE(DirtyColumn AS INT)
Happy Sqling!