Concatenate with Null Values in Sql

Concatenate with NULL values in SQL

Use the COALESCE function to replace NULL values with an empty string.

SELECT Column1 + COALESCE(Column2, '') AS Result
    FROM YourTable

Concatenate multiple columns, with null values

In the absence of CONCAT() (SQL Server 2008 R2+) you can use ISNULL() like so:

SELECT 
    t.id ,
    ISNULL(t.kw1 + ';', '') + ISNULL(t.kw2 + ';', '') +
    ISNULL(t.kw3 + ';', '') + ISNULL(t.kw4 + ';', '') +
    ISNULL(t.kw5 + ';', '') AS Vals
FROM
    @tbl AS t;

If the column value is NULL then the joining of NULL + ';' would produce NULL, therefore giving you the empty string instead ''.

For 2008 R2+ you'd use CONCAT() like so:

SELECT 
    t.id ,
    CONCAT(t.kw1 + ';' ,t.kw2 + ';',t.kw3 + ';' ,t.kw4 + ';', t.kw5+ ';') as Vals
FROM 
    @tbl AS t

Both produce this result:

id          Vals
----------- -----------------------------------------------
1           innocence;graphic novel;cartoon;comics;
2           tattoos;comics;
3           music;cartoon;adventure;film;

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.

CONCAT'ing NULL fields

Try

ISNULL(FirstName, '<BlankValue>') -- In SQL Server
IFNULL(Firstname, '<BlankValue>') -- In MySQL

So,

CONCAT(ISNULL(FirstName,''),ISNULL(LastName,''),ISNULL(Email,'')) -- In SQL Server
CONCAT(IFNULL(FirstName,''),IFNULL(LastName,''),IFNULL(Email,'')) -- In MySQL

would return the same thing without the null issue (and a blank string where nulls should be).

How do I concatenate only if not null in SQL Server?

Hmmm. You can use + instead of concat():

select 'A' + convert(varchar(255), id), name
from t;

convert() (or cast()) is necessary assuming that id is a number and not a string.

+ returns NULL if any argument is NULL; concat() ignores NULL arguments.

Of course, you can use concat() with a case expression:

select (case when id is not null then concat('A', id) end), name
from t;

Concatenating null to string using + results in null

If you concatenate strings with +, string + NULL yields NULL.

If you concatenate strings with &, string & NULL yields string.

Thus, you have two options to fix this:

Option 1: CurrencyName + ' (' + Nz(CurrencySymbol, '') + ')'. Nz (NullToZero) converts Null values to its second argument.

Option 2: CurrencyName & ' (' & CurrencySymbol & ')'

You can use this fact to create an expression that only shows the parenthesis when a currency symbol is present (Credit for this idea goes to this blog post):

CurrencyName & (' (' + CurrencySymbol + ')') will yield Points and Euro (€).

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:

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

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

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