How to keep NULL values in STRING

Please excuse formatting, this is my first post.

I have a table like the one below:

id	code	Fig
1	AAA	MB010@2-1-2-5A@2-2-3
2	AAB	MB010@2-3-4-2@2-2A-2-4
3	AABA	NULL
4	AAC	MB020@2-5-3A

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:

id	code	codePub	FigRef
1	AAA	MB010	2-1-2-5A
1	AAA	MB010	2-2-3
2	AAB	MB010	2-3-4-2
2	AAB	MB010	2-2A-2-4
4	AAC	MB020	2-5-3A

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

id	code	codePub	FigRef
1	AAA	MB010	2-1-2-5A
1	AAA	MB010	2-2-3
2	AAB	MB010	2-3-4-2
2	AAB	MB010	2-2A-2-4
3	AABA	NULL	NULL
4	AAC	MB020	2-5-3A

How can I keep the codes with NULL Fig values?

CodePudding user response：

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

CodePudding user response：

it should be an easy as just putting isnull around the splitting values:

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

CodePudding user response：

You might be able to use a union here:

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%'

UNION ALL

SELECT id, code, Fig, NULL
FROM [dbo].[sourceData]
WHERE Fig IS NULL
ORDER BY id;