It might surprise people, but SQL Server CAST/TRY_CAST and CONVERT/TRY_CONVERT will change an empty string (‘’ or N’’) to a default value instead of NULL. This happens for numeric data types such as int, bigint, smallint, tinyint, numeric, float, bit, datetime, smalldatetime, datetime2, datetimeoffset. Also note that some data types will throw an exception on empty string such as decimal, numeric, and uniqueidentifier.
If you want to return NULL instead of 0, then use NULLIF first, e.g. CONVERT(int, NULLIF(@variable, ‘’))
DECLARE @a nvarchar = ''
-- ==== Example 1A: int (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'int', title = 'CONVERT' , value = CONVERT(int, @a)
UNION SELECT data_type = 'int', title = 'TRY_CONVERT' , value = TRY_CONVERT(int, @a)
UNION SELECT data_type = 'int', title = 'CAST' , value = CAST(@a AS int)
UNION SELECT data_type = 'int', title = 'TRY_CAST' , value = TRY_CAST(@a AS int)
-- Returns
-- data_type | title | value
-- --------- | ----------- | -----
-- int | CAST | 0
-- int | CONVERT | 0
-- int | TRY_CAST | 0
-- int | TRY_CONVERT | 0
-- ==== Example 1B: int (NULLIF) ====
-- Returns NULL
SELECT data_type = 'int', title = 'CONVERT+NULLIF' , value = CONVERT(int, NULLIF(@a, ''))
UNION SELECT data_type = 'int', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(int, NULLIF(@a, ''))
UNION SELECT data_type = 'int', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS int)
UNION SELECT data_type = 'int', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS int)
-- Returns
-- data_type | title | value
-- --------- | ------------------ | -----
-- int | CAST+NULLIF | NULL
-- int | CONVERT+NULLIF | NULL
-- int | TRY_CAST+NULLIF | NULL
-- int | TRY_CONVERT+NULLIF | NULL
-- ==== Example 2A: datetime (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'datetime', title = 'CONVERT' , value = CONVERT(datetime, @a)
UNION SELECT data_type = 'datetime', title = 'TRY_CONVERT' , value = TRY_CONVERT(datetime, @a)
UNION SELECT data_type = 'datetime', title = 'CAST' , value = CAST(@a AS datetime)
UNION SELECT data_type = 'datetime', title = 'TRY_CAST' , value = TRY_CAST(@a AS datetime)
-- Returns
-- data_type | title | value
-- --------- | ----------- | -----------------------
-- datetime | CAST | 1900-01-01 00:00:00.000
-- datetime | CONVERT | 1900-01-01 00:00:00.000
-- datetime | TRY_CAST | 1900-01-01 00:00:00.000
-- datetime | TRY_CONVERT | 1900-01-01 00:00:00.000
-- ==== Example 2B: datetime (NULLIF) ====
-- Returns NULL
SELECT data_type = 'datetime', title = 'CONVERT+NULLIF' , value = CONVERT(datetime, NULLIF(@a, ''))
UNION SELECT data_type = 'datetime', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(datetime, NULLIF(@a, ''))
UNION SELECT data_type = 'datetime', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS datetime)
UNION SELECT data_type = 'datetime', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS datetime)
-- Returns
-- data_type | title | value
-- --------- | ------------------ | -----
-- datetime | CAST+NULLIF | NULL
-- datetime | CONVERT+NULLIF | NULL
-- datetime | TRY_CAST+NULLIF | NULL
-- datetime | TRY_CONVERT+NULLIF | NULL
-- ==== Example 3A: float (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'float', title = 'CONVERT' , value = CONVERT(float, @a)
UNION SELECT data_type = 'float', title = 'TRY_CONVERT' , value = TRY_CONVERT(float, @a)
UNION SELECT data_type = 'float', title = 'CAST' , value = CAST(@a AS float)
UNION SELECT data_type = 'float', title = 'TRY_CAST' , value = TRY_CAST(@a AS float)
-- Returns
-- data_type | title | value
-- --------- | ----------- | -----
-- float | CAST | 0
-- float | CONVERT | 0
-- float | TRY_CAST | 0
-- float | TRY_CONVERT | 0
-- ==== Example 3B: float (NULLIF) ====
-- Returns NULL
SELECT data_type = 'float', title = 'CONVERT+NULLIF' , value = CONVERT(float, NULLIF(@a, ''))
UNION SELECT data_type = 'float', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(float, NULLIF(@a, ''))
UNION SELECT data_type = 'float', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS float)
UNION SELECT data_type = 'float', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS float)
-- Returns
-- data_type | title | value
-- --------- | ------------------ | -----
-- float | CAST+NULLIF | NULL
-- float | CONVERT+NULLIF | NULL
-- float | TRY_CAST+NULLIF | NULL
-- float | TRY_CONVERT+NULLIF | NULL
-- ==== Example 4A: decimal (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'decimal', title = 'CONVERT' , value = CONVERT(decimal, @a)
UNION SELECT data_type = 'decimal', title = 'TRY_CONVERT' , value = TRY_CONVERT(decimal, @a)
UNION SELECT data_type = 'decimal', title = 'CAST' , value = CAST(@a AS decimal)
UNION SELECT data_type = 'decimal', title = 'TRY_CAST' , value = TRY_CAST(@a AS decimal)
-- Throws Error 8114: Error converting data type nvarchar to numeric.
-- ==== Example 4B: decimal (NULLIF) ====
-- Returns NULL
SELECT data_type = 'decimal', title = 'CONVERT+NULLIF' , value = CONVERT(decimal, NULLIF(@a, ''))
UNION SELECT data_type = 'decimal', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(decimal, NULLIF(@a, ''))
UNION SELECT data_type = 'decimal', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS decimal)
UNION SELECT data_type = 'decimal', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS decimal)
-- Returns
-- data_type | title | value
-- --------- | ------------------ | -----
-- decimal | CAST+NULLIF | NULL
-- decimal | CONVERT+NULLIF | NULL
-- decimal | TRY_CAST+NULLIF | NULL
-- decimal | TRY_CONVERT+NULLIF | NULL
-- ==== Example 5A: numeric (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'numeric', title = 'CONVERT' , value = CONVERT(numeric, @a)
UNION SELECT data_type = 'numeric', title = 'TRY_CONVERT' , value = TRY_CONVERT(numeric, @a)
UNION SELECT data_type = 'numeric', title = 'CAST' , value = CAST(@a AS numeric)
UNION SELECT data_type = 'numeric', title = 'TRY_CAST' , value = TRY_CAST(@a AS numeric)
-- Throws Error 8114: Error converting data type nvarchar to numeric.
-- ==== Example 5B: numeric (NULLIF) ====
-- Returns NULL
SELECT data_type = 'numeric', title = 'CONVERT+NULLIF' , value = CONVERT(numeric, NULLIF(@a, ''))
UNION SELECT data_type = 'numeric', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(numeric, NULLIF(@a, ''))
UNION SELECT data_type = 'numeric', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS numeric)
UNION SELECT data_type = 'numeric', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS numeric)
-- Returns
-- data_type | title | value
-- --------- | ------------------ | -----
-- numeric | CAST+NULLIF | NULL
-- numeric | CONVERT+NULLIF | NULL
-- numeric | TRY_CAST+NULLIF | NULL
-- numeric | TRY_CONVERT+NULLIF | NULL
-- 6: data_type = uniqueidentifier
-- ==== Example 6A: uniqueidentifier (empty string) ====
-- Returns 0 instead of NULL
SELECT data_type = 'uniqueidentifier', title = 'CONVERT' , value = CONVERT(uniqueidentifier, @a)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CONVERT' , value = TRY_CONVERT(uniqueidentifier, @a)
UNION SELECT data_type = 'uniqueidentifier', title = 'CAST' , value = CAST(@a AS uniqueidentifier)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CAST' , value = TRY_CAST(@a AS uniqueidentifier)
-- Throws Error 8169: Conversion failed when converting from a character string to uniqueidentifier.
-- ==== Example 6B: uniqueidentifier (NULLIF) ====
-- Returns NULL
SELECT data_type = 'uniqueidentifier', title = 'CONVERT+NULLIF' , value = CONVERT(uniqueidentifier, NULLIF(@a, ''))
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CONVERT+NULLIF', value = TRY_CONVERT(uniqueidentifier, NULLIF(@a, ''))
UNION SELECT data_type = 'uniqueidentifier', title = 'CAST+NULLIF' , value = CAST(NULLIF(@a, '') AS uniqueidentifier)
UNION SELECT data_type = 'uniqueidentifier', title = 'TRY_CAST+NULLIF' , value = TRY_CAST(NULLIF(@a, '') AS uniqueidentifier)
-- Returns
-- data_type | title | value
-- ---------------- | ------------------ | -----
-- uniqueidentifier | CAST+NULLIF | NULL
-- uniqueidentifier | CONVERT+NULLIF | NULL
-- uniqueidentifier | TRY_CAST+NULLIF | NULL
-- uniqueidentifier | TRY_CONVERT+NULLIF | NULL
-- ==== JSON ====
-- NOTE: Empty string will default to integer 0 instead of NULL
DECLARE @json nvarchar(max) = N'[
{"a":0},
{"a":1, "b":2 },
{"a":3, "b":""}
]'
SELECT a
, b
FROM OPENJSON(@json)
WITH (
a int
, b int
)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | 0
-- NOTE: Must manually convert string to integer to avoid empty string defaulting to 0
SELECT a = TRY_CONVERT(int, NULLIF(a, ''))
, b = TRY_CONVERT(int, NULLIF(b, ''))
FROM OPENJSON(@json)
WITH (
a varchar(30)
, b varchar(30)
)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | NULL
-- ==== XML ====
-- NOTE: Empty string will default to integer 0 instead of NULL
DECLARE @xml xml = N'
|
|
|
'
SELECT a = t.x.value('@a', 'int')
, b = t.x.value('@b', 'int')
FROM @xml.nodes('/xml/row') AS t(x)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | 0
-- NOTE: Must manually convert string to integer to avoid empty string defaulting to 0
SELECT a = TRY_CONVERT(int, NULLIF(t.x.value('@a', 'varchar(30)'), ''))
, b = TRY_CONVERT(int, NULLIF(t.x.value('@b', 'varchar(30)'), ''))
FROM @xml.nodes('/xml/row') AS t(x)
-- Returns:
-- a | b
-- 0 | NULL
-- 1 | 2
-- 3 | NULL