SQL query for null value-CodePudding

I have a SQL function(SplitString) which returns string value by considering the separator(;) string to split the string. After getting the string one by one, I want to query in my table by using WHERE AND IN clause. But I have noticed it is not querying when the value is null. I am trying to figure out how to resolve it. My function(SplitString) takes 2 parameters. First the string and the separator. If I do the query, it returns empty string as NULL and other value.

SELECT [Value] FROM ACDC.SplitString(N';01e178bd-8f6c-488f-a0fa-81ffb598e569', ';') s

So from the above query it will return NULL AND 01e178bd-8f6c-488f-a0fa-81ffb598e569 :

Now I want to call a table with this query,

select * from Modeltable where ModelName IN (SELECT 
        [Value]
        FROM ACDC.SplitString(N';01e178bd-8f6c-488f-a0fa-81ffb598e569', ';') s)

In Modeltable, I have also null value which I want to fetch as well. What I understand that sql IN operator ignore the NULL value here. In this case, what should be the correct way to get data both for null and other string value?

CodePudding user response：

You can use an EXISTS query and simple AND OR logic.

SELECT mt.*
FROM Modeltable mt
WHERE EXISTS (SELECT 1
    FROM ACDC.SplitString(N';01e178bd-8f6c-488f-a0fa-81ffb598e569', ';') s
    WHERE s.Value = mt.ModelName
       OR s.Value IS NULL AND mt.ModelName IS NULL
);

In modern versions of SQL Server, you can use STRING_SPLIT and IS NOT DISTINCT FROM

SELECT mt.*
FROM Modeltable mt
WHERE EXISTS (SELECT 1
    FROM STRING_SPLIT(N';01e178bd-8f6c-488f-a0fa-81ffb598e569', ';') s
    WHERE NULLIF(s.Value, '') IS NOT DISTINCT FROM mt.ModelName
);

Note that STRING_SPLIT returns '' not NULL for an empty separator section, which is why you need to null it out before you compare it.

CodePudding user response：

You have to convert the NULL values on both sides of the expression to something that will compare normally:

...
where coalesce(ModelName,'') IN (SELECT 
    coalesce([Value], '') ...

Note this breaks index use on the ModelName column, so it's better to plan ahead to not need to compare NULL at all when possible.