SQL Server SQL query for null value

ezykj2lf 于 2023-11-16 发布在其他

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

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）举报 2023-11-16

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）举报 2023-11-16

我来回答

SQL Server SQL query for null value

2条答案

相关问题

热门标签

最新问答