TSQL - Cast string to integer or return default value

Question

Hi!

Is there a way in T-SQL to cast an nvarchar to int and return a default value or NULL if the conversion fails?

Best Regards
Oliver Hanappi

Answer 1

Yes :). Try this:

DECLARE @text AS NVARCHAR(10)

SET @text = '100'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns 100

SET @text = 'XXX'
SELECT CASE WHEN ISNUMERIC(@text) = 1 THEN CAST(@text AS INT) ELSE NULL END
-- returns NULL

Answer 2

I would rather create a function like TryParse and use sql TRY-CATCH block to get what you wanted.

ISNUMERIC doesn't always work as intended. The code given before will fail if you do:

SET @text = '$'

$ sign can be converted to money datatype, so ISNUMERIC returns true in that case. It will do the same for '-' (minus) character.