CAST/CONVERT empty string to INT in SQL Server

Question

I came across a bug where I was using CAST(Col1 AS INT) + CAST(Col2 AS INT) where both Col1 and Col2 are VARCHAR and I was getting valid results out when Col1 or Col2 was blank and I didn't expect this. I checked and CAST (and CONVERT) both have this default behavior of replacing blank with 0:

SELECT CAST('' AS INT)
SELECT CONVERT(INT, '')

I checked the info page and I can't see any reference to explain why this is the behavior (or change it through a server setting). I can of course work around this but I wanted to ask why this is the behavior as I do not think it is intuitive.

I'd actually rather this CAST failed or gave NULL, is there a server setting somewhere which effects this?

Answer 1

Consider an INT in SQL Server. It can be one of three values:

• NULL
• 0
• Not 0

So if you're casting/converting an empty string, which you are assuming is a number, then 0 is the most logical value. It allows for a distinction between NULL and 0.

SELECT CAST(NULL AS INT) -- NULL
SELECT CAST('' AS INT)   -- 0
SELECT CAST('42' AS INT) -- 42

I'd say that's logical.

If you did:

SELECT CAST('abc' AS INT)

You'd get:

Conversion failed when converting the varchar value 'abc' to data type int.

If you do wish to handle empty strings as NULL use NULLIF as Bogdan suggests in his answer:

DECLARE @val VARCHAR(2) = ''

SELECT CAST(NULLIF(@val,'') AS INT)  -- produces NULL

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

Finally, if your columns are storing INT values, then consider changing its data type to INT if you can.

Answer 2

Assuming you what to get NULL when an empty string is converted to INT then I would use one of following solutions:

DECLARE @SourceString VARCHAR(50) = ''

SELECT CONVERT(INT, NULLIF(@SourceString, ''))  AS Solution1
SELECT TRY_PARSE(@SourceString AS INT)          AS Solution2