I'm using SQL Server 2019.
select convert(bigint, ' ')
returns 0
select convert(numeric, ' ')
returns:
Error converting data type varchar to numeric.
Can anyone tell me why the behaviour between these two datatypes is different? I would have expected, that the second example returns 0 or 0.00.
From CAST and CONVERT:
"SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal."
It's the documented behavior, that's why.
It's just the way it is implemented. Casting an empty string or a string with only spaces to an INT data type will result in 0. Some people regard this as logical behavior, others don't.
For a DECIMAL type, the same cast fails.
However, this is only true for spaces! Trying to cast other whitespace characters like tabs, carriage return or linefeed characters to an INT type will also fail.
If you want failed casts to return NULL instead of producing an error, you can use TRY_CONVERT instead:
select try_convert(numeric, ' ')
If you want both to behave in the same way (whether that would mean both returning NULL or both returning 0 or 0.0 respectively) you need to somehow work your way around it.
To have the cast to INT return NULL, you could e.g. combine TRY_CONVERT with NULLIF:
select try_convert(bigint, nullif(' ', ''))
This will also produce NULL for strings containing only (any number of) spaces.
If on the other hand, you prefer to see 0.0 results for empty strings or strings containing only spaces when casting to NUMERIC, you can filter these out explicitly:
select case when val like ' ' or val = '' then 0.0
else try_convert(numeric(5,1), val)
end as val
from strings
This db<>fiddle shows what results the different queries yield for various whitespace and non-whitespace string input.