How to keep NULL values in STRING_SPLIT

Please excuse formatting, this is my first post.

I have a table like the one below:

My code is as follows:

  SELECT 
     source.id
    ,source.code
    ,codePub = LEFT(source.Fig,5)
    ,f.value AS [FigRef]
  FROM [dbo].[sourceData] AS source
  OUTER APPLY STRING_SPLIT(source.[Fig], '@') as f
  WHERE f.value NOT LIKE 'MB%'

Which gives me the following table:

But I want the codes with NULL values as well, like below:

How can I keep the codes with NULL Fig values?

>Solution :

Your WHERE clause filters NULLs out.

NULL LIKE 'MB%' is "unknown", which is not "true" in the context of the WHERE clause, so these rows are filtered out.

NULL NOT LIKE 'MB%' is still "unknown", which is still not "true" in the context of the WHERE clause, so these rows are also filtered out.

NULL = NULL is "unknown", NULL IS NULL is "true".

Comparison Operators

Comparison operators test whether two expressions are the same.

The result of a comparison operator has the Boolean data type. This
has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a
Boolean data type are known as Boolean expressions.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN.

You need to explicitly compare with NULL using IS operator. For example, like this:

  SELECT 
     source.id
    ,source.code
    ,codePub = LEFT(source.Fig,5)
    ,f.value AS [FigRef]
  FROM 
      [dbo].[sourceData] AS source
      OUTER APPLY STRING_SPLIT(source.[Fig], '@') as f
  WHERE 
      f.value NOT LIKE 'MB%' OR f.value IS NULL