SQL Server SQL query for null value

ezykj2lf 于 12个月前发布在其他

关注(0)|答案(2)|浏览(96)

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?

sql-server

来源：https://stackoverflow.com/questions/77410579/sql-query-for-null-value

2条答案

按热度按时间

vxqlmq5t1#

You can use an EXISTS query and simple ANDOR 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.

赞(0）回复(0）举报 12个月前

u5rb5r592#

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.

赞(0）回复(0）举报 12个月前

我来回答

SQL Server SQL query for null value

2条答案

相关问题

热门标签

最新问答