CODEWITHSUNDEEP
sqlsql-servernullstring-concatenationnull-coalescing
Jens Mühlenhoff
Jens Mühlenhoff

Reputation: 14873

How to concat strings with space delimiter where each string is nullable?

I have a table with multiple nullable string fields.

For reporting I would like to combine them into a single string that is delimited with a space for every field. The right side must be trimmed.

Test data:

DECLARE
  @test 
TABLE
(
    f1 NVARCHAR(100)
  , f2 NVARCHAR(100)
  , f3 NVARCHAR(100)
)

INSERT INTO @test 
-- NULL values must be skipped
      SELECT NULL       , NULL , NULL
UNION SELECT NULL       , NULL , 'foo'
UNION SELECT NULL       , 'foo', NULL
UNION SELECT 'foo'      , NULL , NULL
UNION SELECT NULL       , 'foo', 'bar'
UNION SELECT 'foo'      , 'bar', NULL
UNION SELECT 'foo'      , NULL , 'bar'
UNION SELECT 'foo'      , 'bar', 'baz'
-- Empty string values must be skipped
UNION SELECT 'foo'      , ''   , 'bar'
UNION SELECT ''         , ''   , 'baz' 
-- Spaces in values must be kept
UNION SELECT 'foo   bar', ''   , 'baz'
-- The table should not contain values with surrounding space
-- So this case is not important
UNION SELECT ' foo '    , ' '  , 'baz '

The expected results:

''
'foo'
'foo'
'foo'
'foo bar'
'foo bar'
'foo bar'
'foo bar baz'
'foo bar'
'baz'
'foo   bar baz'
'foo  baz'
' foo   baz' -- This is optional

My current code: 

SELECT
  RTRIM(
    CASE WHEN ISNULL(f1, '') = '' THEN '' ELSE f1 + ' ' END
  + CASE WHEN ISNULL(f2, '') = '' THEN '' ELSE f2 + ' ' END
  + CASE WHEN ISNULL(f3, '') = '' THEN '' ELSE f3 + ' ' END
  )
FROM
  @test

This works, but I was wondering if there is a less verbose way to achieve this?

Upvotes: 0

Views: 434

Answers (3)

Sumon Barua
Sumon Barua

Reputation: 57

In SQL Server 2012, there is a CONCAT function. You can use the following as well:

SELECT
  RTRIM(CONCAT(  
      NULLIF(f1, '') + ' '
    , NULLIF(f2, '') + ' '
    , NULLIF(f3, '') + ' '
  ))
FROM
  @test

Upvotes: 1

Akalanka
Akalanka

Reputation: 310

Well, so easy after podiluska's answer.

select 
    LTRIM(RTRIM(isnull(f1+' ','') + 
    isnull(f2+' ','') + 
    isnull(f3+' ','') ))
from @test

Upvotes: 0

podiluska
podiluska

Reputation: 51504

Because null + a space is still null, you can use 

select 
    isnull(nullif(f1,'')+' ','') + 
    isnull(nullif(f2,'')+' ','') + 
    isnull(nullif(f3,'')+' ','') 
from @test

Upvotes: 1

Related Questions