問題描述

我希望這個(gè)查詢返回 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)

這個(gè)例子來自https://stackoverflow.com/a/39752683/1903793

來自答案:SQL拆分字符串并獲得 NULL 值而不是空字符串 我知道它可以通過使用 NULLIF 函數(shù)輕松包裝來修復(fù):

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 變量 進(jìn)行操作來修復(fù)，而不是之后進(jìn)行操作.

However I wonder if it might be alternatively fixed by manipulating with XML variable directly, not afterwards.

注意.我的問題遵循 SQL 拆分字符串并獲取 NULL 值而不是空字符串請(qǐng)不要將其標(biāo)記為欺騙，因?yàn)槲疫€沒有收到 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.詳情請(qǐng)看他的評(píng)論.

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)

這篇關(guān)于在 XML 變量中用 NULL 替換空字符串的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！