How to handle Empty string and NULL the same way in SQL Server-CodePudding

I need the data from four columns (A,B,C,D) in a table. If the columns are non-empty or not null, the I will select the data as result. So, the result should be in the format

A:11,C:03

A:12,B:87,C:24,D:01

or just

' '

based on the values the columns hold. My query is treating empty as NOT NULL value and returning me different result than what is expected.

I am expecting D:Remote for both rows. Whereas I am getting A:,B:,C:,D:Remote

HEre is the fiddle : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cb708aebe2220915f6781864910a3f5b

CodePudding user response：

…

select concat_ws(',', 'a:' nullif(a,''), 'b:' nullif(b,''), 'c:' nullif(c,''), 'd:' nullif(d,'') )
from t;


select isnull(stuff(concat(',a:' nullif(a,''), ',b:' nullif(b,''), ',c:' nullif(c,''), ',d:' nullif(d,'')), 1, 1, ''), '')
from t;

CodePudding user response：

To get the result you want you need to add another case looking like this when A is null and B is null and C is null and D is not null then 'D:' D. The issue you're having is that your case doesn't return anything which results in a NULL.

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (A varchar(50) , B varchar(50) , C varchar(50) , D varchar(50));
INSERT INTO @tbl VALUES 
( '' , '' , '', 'Remote'),
( '' , 'Local' , '', 'Remote'),
( null , null , null, 'Remote');
-- DDL and sample data population, end

SELECT A, B, C, D
    , REPLACE(x.query('for $x in /root/*[string-length(text()[1]) gt 0]
            return concat(local-name($x), ":", $x)')
            .value('.','VARCHAR(MAX)'), SPACE(1), ',') AS Result
FROM @tbl
CROSS APPLY (SELECT A, B, C, D
    FOR XML PATH(''), TYPE, ROOT('root')) AS t(x)

Output

 ------------------ 
|      Result      |
 ------------------ 
| D:Remote         |
| B:Local,D:Remote |
| D:Remote         |
 ------------------