CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Rominus
Rominus

Reputation: 1221

How to efficiently concatenate fields that may be null?

I have a front-end field in which the user should see the values of several fields, concatenated with commas. Which is easy enough if the fields are always populated, but that is not the case. In order to avoid extra commas and spaces I came up with:

concat( 
    [field_one], 
    case when [field_one] is not null and [field_two] is not null then ', ' end, 
    [field_two], 
    case when ([field_two] is not null or [field_one] is not null) and [field_three] is not null then ', ' end, 
    [field_three]
) as [list_field]

Which works well enough for three fields. But the specs have changed and more fields will be added. The case statements will quickly get out of hand since they're referencing all previous fields. Is there an efficient way to do something like this?

Upvotes: 1

Views: 95

Answers (4)

Jason A. Long
Jason A. Long

Reputation: 4442

You can actually take advantage of the fact that NULL + anything = NULL and the CONCAT function's NULL handling ability. It makes for a cleaner syntax without all of the COALESCE / ISNULL gunk...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    Col_1 VARCHAR(20) NULL,
    Col_2 VARCHAR(20) NULL,
    Col_3 VARCHAR(20) NULL 
    );

INSERT #TestData (Col_1, Col_2, Col_3) VALUES
    ('Bob', 'A.', 'Jones'),
    ('123 Some St.', NULL, 'Jacksonville'),
    (NULL, 'R.', 'Smith'),
    ('Roy', 'Finch', NULL),
    (NULL, NULL, 'Prince'),
    (NULL, NULL, NULL),
    ('Arnold', NULL, NULL);

SELECT 
    ConcatString = CONCAT(td.Col_1 + ' ', td.Col_2 + ' ', td.Col_3)
FROM 
    #TestData td;

Output...

ConcatString
--------------------------------------------------------------
Bob A. Jones
123 Some St. Jacksonville
R. Smith
Roy Finch 
Prince

Arnold

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

The NullIf() is to trap empty/non-null values. Optional if you only have nulls and values.

Example

Declare @YourTable table (field_one varchar(50),field_two varchar(50),field_three varchar(50))
Insert Into @YourTable values
 ('a','b','c')
,('a',null,'c')
,('a',null,null)
,('a','b',null)
,(null,'b',null)

Select *
      ,list_field = stuff( concat( 
                                   ', '+nullif([field_one],'')
                                  ,', '+nullif([field_two],'')
                                  ,', '+nullif([field_three],'')
                                  )
                         ,1,2,'')
 From @YourTable

Returns

field_one   field_two   field_three list_field
a           b           c           a, b, c
a           NULL        c           a, c
a           NULL        NULL        a
a           b           NULL        a, b
NULL        b           NULL        b

Upvotes: 2

lit
lit

Reputation: 16236

I have not tested it, but I believe that this will work on any ANSI compliant database. This probably presumes that the fields are CHAR type or that you will convert them with something like CONVERT() in SQL Server or TO_CHAR() in Oracle.

SELECT
    CONCAT(
        COALESCE(field_one, '')
        ,',',COALESCE(field_two, '')
        ,',',COALESCE(field_three, '')
    )
FROM ...

Here is a question about STUFF(). Oracle equivalent to SQL Server STUFF function?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is one method:

select stuff( coalesce(', ' + field_one, '') +
              coalesce(', ' + field_two, '') +
              coalesce(', ' + field_three, ''), 1, 2, '')

The three expressions add a ', ' before each non-null field. The stuff() removes the first one.

Upvotes: 2

Related Questions