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?

It is basic mathematics. An `integer` cannot be **blank**, though in SQL DB it can be `NULL`. — G J, May 27 '16 at 08:31
I wasn't trying to state that this behaviour in SQL is a bug, it was a bug in our product which led me to find this behaviour and I question whether I can change it because I think this is not intuitive behaviour — Nick, May 31 '16 at 07:40

score 22 · Answer 1 · edited Apr 19 '18 at 11:04

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.

Sorry but I don't agree. 0 is a valid number like so I don't see why it should be treated as a special case and I think an INT is either NULL or a whole number. I think it would be more intuitive for the cast to fail with a blank string because a blank string is not NULL nor is it a valid number. If this is the logical behaviour then why does the same action with the DECIMAL data type produce an error instead of 0? — Nick, May 31 '16 at 07:47
even more interesting: SELECT CAST('true' AS [bit]) -- 1 SELECT CAST('false' AS [bit]) -- 0 SELECT CAST('' AS [bit]) -- 0 — Oleksandr, Mar 16 '22 at 10:38

score 0 · Answer 2 · answered May 27 '16 at 08:44

As you probably know NULL is a marker that indicates that a data value does not exist. And '' is a value, empty but value.

So MS SQL cast (or converts) empty value into 0 by default. To overcome this and show it as NULL you can use NULLIF

Simple example:

SELECT  int_as_varchars as actual,
        cast(NULLIF(int_as_varchars,'') as int) as with_nullif,
        cast(int_as_varchars as int) as just_cast
FROM (VALUES
('1'),
(''),
(NULL),
('0')
) as t(int_as_varchars)

Output:

actual  with_nullif just_cast
1       1           1
        NULL        0
NULL    NULL        NULL
0       0           0

As you see NULLIF in that case will help you to get NULL instead of 0.

score 0 · Answer 3 · answered Nov 02 '18 at 16:23

What about this ?

declare @t table(bucket bigint);

INSERT INTO @t VALUES (1);
INSERT INTO @t VALUES (2);
INSERT INTO @t VALUES (-1);
INSERT INTO @t VALUES (5);
INSERT INTO @t VALUES (0);

declare @Bucket bigint = 0 --filter by 0

select * from @t
where 1=1
AND ((@Bucket is Null or cast(@Bucket as nvarchar) = '') or bucket=@Bucket)

CAST/CONVERT empty string to INT in SQL Server

3 Answers3

Linked