CODEWITHSUNDEEP
sql-serverstored-proceduressql-function
Riddhish Bambhroliya
Riddhish Bambhroliya

Reputation: 69

Is there any way to return null in JSON_VALUE instead of empty in SQL Server

My sample code is like this:

DECLARE @JsonText varchar(MAX) = '{
    "Test": ""
}'

DECLARE @Name varchar(MAX) = JSON_VALUE(@JsonText, '$.Test') ;

IF (@Name IS NULL)
BEGIN
    SELECT 'Null' AS Result
END
ELSE
BEGIN 
    SELECT 'Empty' AS Result
END

and I got an output:

Result
----------
Empty

My expected output is Null, I have tried with trim and then check string is empty then return null is working fine.

IF (@Result = '') 
    SET @Result = NULL;

But I am looking for any other alternative if available which returns null instead of empty.

Upvotes: 0

Views: 3348

Answers (1)

Thom A
Thom A

Reputation: 95830

A zero length string (in JSON "" and in T-SQL '') and NULL are not the same value. In your JSON you have a zero length string. If you want a NULL value to be returned from your JSON then define the value as Null in your JSON:

DECLARE @JsonText nvarchar(MAX) = N'{
    "Test": null
}';

DECLARE @Name nvarchar(MAX) = JSON_VALUE(@JsonText, '$.Test');
IF (@Name IS NULL)
BEGIN
    SELECT 'Null' AS Result;
END
ELSE
BEGIN 
    SELECT 'Empty' AS Result;
END;

This returns the varchar value 'Null'.

Alternatively, you could use NULLIF to return NULL if your expression returns a zero length string, but I would suggest that really it's the JSON that needs correcting:

SET @Name = NULLIF(JSON_VALUE(@JsonText, '$.Test'),N'');

Upvotes: 4

Related Questions