Why does my NOT LIKE '%%' query return incorrect results?-CodePudding

I have this query

select * 
from Table
where 
    JsonColumn like '%A%' and
    JsonColumn not like '%"A": null%'

I can see that the JsonColumn column of several rows contains the 2nd value mentioned, i.e. its a json string including this property like

{
    "A": null
}

Why are those rows not being filtered out?

For example, my JsonColumn could look like any of the below:

{ "A": "2022-10-20" }
{ "A": null }
{ }

So I will try to run the query SELECT * FROM Table WHERE JsonColumn NOT LIKE '%"A": null%', and expect it to return only the first and third rows, since they don't contain "A": null

CodePudding user response：

I would use OPENJSON to handle JSON data. JSON_QUERY can not be used in your example since it does not differentiate between a key containing null and a key that does not exist:

select *
from t
where not exists (
    select *
    from openjson(jsoncolumn)
    where [key] = 'A' and value is null
)

CodePudding user response：

You must use like this you can check your column for null using Like in sql. It will return the records which are not like DateOnBlacklist.

select * from [dbo].[EntityChanges] where BaselineJsonString not like coalesce('