在 XML 变量中用 NULL 替换空字符串
我希望这个查询返回 NULL 而不是空字符串.
I wish this query would return NULL instead of empty string.
declare @str varchar(max)='A,,C;D,E,F;X,Y,Z'; -- please notice missing B
declare @xmlstr XML
set @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)
SELECT
t.n.value('col[1]','nvarchar(max)') as Col1
,t.n.value('col[2]','nvarchar(max)') as Col2
,t.n.value('col[3]','nvarchar(max)') as Col3
FROM
@xmlstr.nodes ('/rows/row') AS t(n)
这个例子来自https://stackoverflow.com/a/39752683/1903793
来自答案:SQL拆分字符串并获得 NULL 值而不是空字符串 我知道它可以通过使用 NULLIF 函数轻松包装来修复:
From the answer: SQL split string and get NULL values instead of empty string I know it can be fixed by easy wrap with NULLIF function:
SELECT
nullif(t.n.value('col[1]','nvarchar(max)'),'') as Col1
,nullif(t.n.value('col[2]','nvarchar(max)'),'') as Col2
,nullif(t.n.value('col[3]','nvarchar(max)'),'') as Col3
但是我想知道它是否可以通过直接使用 XML 变量 进行操作来修复，而不是之后进行操作.
However I wonder if it might be alternatively fixed by manipulating with XML variable directly, not afterwards.
注意.我的问题遵循 SQL 拆分字符串并获取 NULL 值而不是空字符串请不要将其标记为欺骗，因为我还没有收到 XML 方法的答案.
Note. My question follows SQL split string and get NULL values instead of empty string Please do not mark it as dupe because I have not received answer for XML method.
推荐答案
此答案的功劳完全归功于 Jeroen Mostert.详情请看他的评论.
The credit for this answer is entirely to Jeroen Mostert. For details please see his comments.
declare @str varchar(max)='A,,C;D,E,F;X,Y,Z'; -- please notice missing B
declare @xmlstr XML
set @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)
SELECT
t.n.value('col[1]/text()[1]','nvarchar(max)') as Col1
,t.n.value('col[2]/text()[1]','nvarchar(max)') as Col2
,t.n.value('col[3]/text()[1]','nvarchar(max)') as Col3
FROM
@xmlstr.nodes ('/rows/row') AS t(n)
相关文章