TSQL - Cast string to integer or return default value

user_128709 0
Viewed 218050

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

tsql sql
8 Answers

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

ISNUMERIC() has a few issues pointed by Fedor Hajdu.

It returns true for strings like $ (is currency), , or . (both are separators), + and -.

ggierlik_14830

I would rather create a function like TryParse or use T-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), ',' (comma) and '.' characters.

user_2431530
Related