[SQL Server] TRY_CAST, TRY_CONVERT

[SQL Server] TRY_CAST, TRY_CONVERT

TRY_CAST and TRY_CONVERT are basically the same as CAST and CONVERT. The big difference is that TRY_CAST and TRY_CONVERT return NULL when they fail to CONVERT data type.


TRY_CAST and TRY_CONVERT are the same but different expression the same as CAST and CONVERT.

CONVERT

DECLARE @INPUT VARCHAR(100)

SET @INPUT = '100,2';

SELECT CONVERT(FLOAT,@INPUT) AS INPUT_CONVERT;

Msg 8114, Level 16, State 5, Line 5 Error converting data type varchar to float.

TRY_CONVERT

DECLARE @INPUT VARCHAR(100)

SET @INPUT = '100,2';

SELECT TRY_CONVERT(FLOAT,@INPUT) AS INPUT_CONVERT;

This is also useful when you have to join 2 tables with the different data types and you can ignore unmatched columns.

e.g.

SELECT
 tbl1.Col1,
 tbl2.Col2
FROM
 Table1 tbl1 INNER JOIN Tabl2 tbl2 ON tbl1.ID = TRY_CONVERT(INT,tbl2.KeyID)