SQL Using If Not Null on a Concatenation

SQL using If Not Null on a Concatenation

Here would be my suggestions:

PostgreSQL and other SQL databases where 'a' || NULL IS NULL, then use COALESCE:

SELECT firstname || COALESCE('-' || middlename, '') || '-' || surname ...

Oracle and other SQL databases where 'a' || NULL = 'a':

SELECT first name || DECODE(middlename, NULL, '', '-' || middlename) || '-' || surname...

I like to go for conciseness. Here it is not very interesting to any maintenance programmer whether the middle name is empty or not. CASE switches are perfectly fine, but they are bulky. I'd like to avoid repeating the same column name ("middle name") where possible.

As @Prdp noted, the answer is RDBMS-specific. What is specific is whether the server treats a zero-length string as being equivalent to NULL, which determines whether concatenating a NULL yields a NULL or not.

Generally COALESCE is most concise for PostgreSQL-style empty string handling, and DECODE (*VALUE*, NULL, ''... for Oracle-style empty string handling.

SQL concatenate if value not null

This should work, including correct handling of commas if any of the fields are empty.

DROP TABLE IF EXISTS #PERSON_DEPT
CREATE TABLE #PERSON_DEPT(F_NAME VARCHAR(10), M_NAME VARCHAR(10), L_NAME VARCHAR(10))

INSERT INTO #PERSON_DEPT (F_NAME, M_NAME, L_NAME) VALUES 
('Max', 'V', 'O'),
('John', null, 'Doe'),
('Alex', 'D', null),
('Spencer', null, null),
(null, null, null)

SELECT 
    COALESCE('FirstName:' + F_NAME, '') + (CASE WHEN M_NAME IS NULL THEN '' ELSE ',' END) + 
    COALESCE('MiddleName:' + M_NAME, '') + (CASE WHEN L_NAME IS NULL THEN '' ELSE ',' END) + 
    COALESCE('LastName:' + L_NAME, '') 
FROM #PERSON_DEPT

The result:

Full Name
FirstName:Max,MiddleName:V,LastName:O
FirstName:John,LastName:Doe
FirstName:Alex,MiddleName:D
FirstName:Spencer
empty

sql concatenate columns if one or more columns is not null

Try this:

select case when [Note] = '' then null else Note from (
    select BulidingId,
            case when RouterType is null then '' else 'Router Type: ' + RouterType + '; '+
            case when RouterPort is null then '' else 'Router Port: ' + RouterPort + '; '+
            case when RouterInstaller is null then '' else 'Router Port: ' + RouterInstaller + '; '+
            case when Notes is null then '' else 'Notes: ' + Notes + '; ' [Note]
    from MY_TABLE
) a

Concatenate with string but exclude when null

Something like this... I added simulated inputs to test all four possibilities. However, note that if you may have last name but no first name, and also first name but no last name, the combined column will show just one name but you will not know if it is first or last. (Also, when a comma is added, I also add a space after the comma, as is usual; that can be controlled easily though.)

with
  tablea ( lastname, firstname, id ) as (
               select 'Smith', 'Ann'  , 1 from dual
     union all select null   , null   , 2 from dual
     union all select 'Ang'  , null   , 3 from dual
     union all select null   , 'Murat', 4 from dual
  )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select lastname, firstname, id,
       lastname 
         || case when lastname is not null and firstname is not null then ', ' end
         || firstname
       as last_first
from   tablea
;

LASTNAME   FIRSTNAME          ID LAST_FIRST 
---------- ---------- ---------- ------------
Smith      Ann                 1 Smith, Ann  
                               2             
Ang                            3 Ang         
           Murat               4 Murat

SQL Server String Concatenation with Null

You can use ISNULL(....)

SET @Concatenated = ISNULL(@Column1, '') + ISNULL(@Column2, '')

If the value of the column/expression is indeed NULL, then the second value specified (here: empty string) will be used instead.

SQL string concatenation, separated by ',' only when column is not null

Well, let me start by saying that keeping delimited string in a single column is a terrible database design, with only one exception: when the data is never used in sql and is needed as a delimited string in the application. In over 16 years of programming, I've only one time stored values like this, and it was exactly this scenario.

However, should you choose to store delimited values, here is something easy to do:

UPDATE ImportState
SET ClientValidationRemark = ISNULL(NULLIF(ClientValidationRemark, '') + ',', '') +
                             @ClientValidationRemark
WHERE Id=@ImportId

Taking advantage of the fact that concatenating strings to null values will result as null (Note: only when you use the string concatenation operator (+), Not when you use the built in function concat), I've used NULLIF to convert empty string values in ClientValidationRemark to null, and then ISNULL to convert the null + ', ' back to an empty string.

SQL Server concatenate ignore null value

The STUFF method is probably the better option here:

STUFF(CONCAT(',' + NULLIF(@item1, ''),',' + NULLIF(@item2, ''),',' + NULLIF(@item3, '')),1,1,'')

SQL Using If Not Null on a Concatenation