CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
oshirowanen
oshirowanen

Reputation: 15925

Concat with NULL value

I have a query which looks something like this:

select distinct col1,
(
    select ltrim(str(col2))+','
    from @temp t2
    where t2.col1 = t1.col1
    for xml path('')
) col2
from @temp t1

Some of the values in col2 are null, so instead of displaying a string with null, that part is missing totally, i.e.

col2 should have

3,4,3,NULL,2,3,4,3,2,

but instead I am getting:

3,4,3,2,3,4,3,2,

How do I correct this?

Upvotes: 1

Views: 148

Answers (4)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

SELECT  DISTINCT col1,
        (
           SELECT ISNULL(CAST(col2 AS VARCHAR(10)),'NULL') + ','
           FROM   @Temp AS T2
           WHERE  T2.col1 = T1.col1
           FOR    XML PATH('')
        ) AS col2
FROM    @Temp AS T1

Add any value to NULL will result NULL

ANY VALUE + NULL = NULL

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

A NULL value is not the string NULL. It is simply absence of information. What you could do is to force a NULL value to be treated as the string NULL for your purposes like so:

SELECT CASE
      WHEN col2 IS NULL THEN 'NULL'
      ELSE col2
      END + ','

To make it more concise, you may use isnull(col2,'NULL') or coalesce(col2,'NULL')

If col2 is not a text type, cast it first before using any of the above solutions like so:

SELECT CASE
      WHEN col2 IS NULL THEN 'NULL'
      ELSE CAST(col2 AS VARCHAR(5))
      END + ','

Upvotes: 1

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

This should work. Hope it was helpful

DECLARE @temp TABLE (col1 INT,col2 INT)

INSERT INTO @temp (col1,col2)
VALUES (1, 2),
      (1, 3),
      (1, 4),
      (1, NULL),
      (1, 6),
      (1, 7)

SELECT DISTINCT col1,
             SUBSTRING((
             SELECT ','+LTRIM(ISNULL(STR(col2),'NULL'))
             FROM @temp AS t2
             WHERE t2.col1 = t1.col1
             FOR XML PATH('')
             ),2,200000) AS col2
FROM @temp AS t1

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

To get a NULL output, you'll need to change the NULL value into the string 'NULL'. That also means that you'll need to cast the numeric value to a string to have a consistent type for the field;

SELECT DISTINCT col1,
(
    SELECT COALESCE(CAST(col2 AS VARCHAR),'NULL')+','
    FROM @temp t2
    WHERE t2.col1 = t1.col1
    FOR XML PATH('')
) col2
FROM @temp t1

An SQLfiddle to test with.

Upvotes: 2

Related Questions