请原谅格式化,这是我的第一篇帖子.
我有一张如下表:
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 |
我的代码如下:
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%'
这给了我下表:
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 |
但我也希望代码具有空值,如下所示:
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 |
如何保持Fig值为空的代码?