CODEWITHSUNDEEP
sql-server
Nick
Nick

Reputation: 171

CAST/CONVERT empty string to INT in SQL Server

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?

Upvotes: 15

Views: 107161

Answers (3)

Othman Dahbi-Skali
Othman Dahbi-Skali

Reputation: 632

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)

Upvotes: 0

Tanner
Tanner

Reputation: 22733

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.

Upvotes: 23

gofr1
gofr1

Reputation: 15977

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.

Upvotes: 0

Related Questions