Concatenate with NULL values in SQL

Question

Column1      Column2
-------      -------
 apple        juice
 water        melon
 banana
 red          berry

I have a table which has two columns. Column1 has a group of words and Column2 also has a group of words. I want to concatenate them with + operator without a space.

For instance: applejuice

The thing is, if there is a null value in the second column, I only want to have the first element as a result.

For instance: banana

Result
------
applejuice
watermelon
banana
redberry

However, when I use column1 + column2, it gives a NULL value if Column2 is NULL. I want to have "banana" as the result.

This is the case with more than just strings I've found. I dont know if its the most efficient but you could always wrap the columns in an isNull statement. ie: isNull(column1, '') + isNull(column2,'') what that should do is when it finds null it will replace it with an empty string. I haven't tested this so let me know if it works. — Chris Santiago
– Chris Santiago, Commented Nov 22, 2011 at 21:03
Possible duplicate of SQL Server String Concatenation with Null — Michael Freidgeim
– Michael Freidgeim, Commented Mar 9, 2016 at 22:15

Joe Stefanelli · Accepted Answer · 2011-11-22 21:03:30Z

78

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

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

answered Nov 22, 2011 at 21:03

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Client Relations · Accepted Answer · 2019-07-09 05:19:49Z

A few posts I have made tagged MSSQL have been renamed to 'SQL' by a moderator. So I am assuming you are using MSSQL

COALESCE will return the FIRST non-null value.

SELECT COALESCE('a', NULL, 'c')

will only return 'a'

If you want Firstname + Lastname, where sometimes one or the other is NULL, use CONCAT. Concat adds the strings together and replaces NULLS with 0 length non-null value.

 SELECT CONCAT('a', NULL, 'c')

will return 'ac'

If you want Fn space + middle name space + LN, combine concatinate with CONCAT:

SELECT CONCAT('a' + ' ', NULL + ' ', 'c')

Will return 'a c'.

The space after middlename (null) is eliminated with the + and NULL.

NULL + ' ' is null.

So in cases where Middlename or Firstname is null, you won't get extra unwanted spaces.

This is perfect! I had to do an RTRIM(LTRIM(x)) on the fields, and then I did a case statement to check if the last character of the concatenation was the delimiter I was using (;) so I could strip that off, in case there was an extra one at the end.
@Client Relations You are not correct actually because SELECT CONCAT('a' + ' ', NULL + ' ', 'c') will return Null not 'ac'

Jonathan Leffler · Accepted Answer · 2011-11-22 21:08:14Z

Standard SQL requires that string concatenation involving a NULL generates a NULL output, but that is written using the || operation:

SELECT a || b
  FROM SomeTable;

The output will be null if either a or b or both contains a NULL.

Using + to concatenate strings indicates that you are using a DBMS-specific extension. The behaviour might be the same as the standard requires - indeed, that seems to be the gist of your question.

Some DBMS - notably Oracle - tend to treat null strings as equivalent to empty strings; then you can concatenate away merrily. However, that behaviour is not strictly standard-compliant if the || operator is used.

Consider using COALESCE or NVL or IFNULL or some similar function to map the NULL to an empty string before concatenating.

jtlz2 · Accepted Answer · 2023-10-20 09:20:43Z

1

If you are using MySql, use ifnull(Column2, '')

edited Oct 20, 2023 at 9:20

jtlz2

8,52711 gold badges74 silver badges128 bronze badges

answered Nov 22, 2011 at 21:10

CapelliC

60.1k5 gold badges50 silver badges94 bronze badges

Comments

Chris E · Accepted Answer · 2011-11-22 21:03:48Z

0

I'm not certain what you're using as your database, but I would look for a "coalesce" function for your particular SQL dialect and use that.

answered Nov 22, 2011 at 21:03

Chris E

98313 silver badges26 bronze badges

Comments

Chris Cooper · Accepted Answer · 2011-11-22 21:18:18Z

0

You can do a union:

(SELECT Column1 + Column2 FROM Table1 WHERE Column2 is not NULL)
UNION
(SELECT Column1 FROM Table1 WHERE Column2 is NULL);

answered Nov 22, 2011 at 21:18

Chris Cooper

17.6k11 gold badges55 silver badges70 bronze badges

Comments

trincot · Accepted Answer · 2017-04-13 23:49:29Z

0

You can use a case condition:

case when column_2 is not null 
     then concatenate
     else column_1
end

edited Apr 13, 2017 at 23:49

trincot

357k38 gold badges282 silver badges338 bronze badges

answered Apr 13, 2017 at 22:03

harsh

11

2 Comments

Adam Over a year ago

this doesn't add anything useful to the other 6 existing answers

trincot Over a year ago

@Adam, actually no-one had yet suggested the case expression as solution. I think your assessment is maybe too harsh.

WebChemist · Accepted Answer · 2023-09-29 12:27:17Z

I know this is old, but since no one mentioned it - if using MySQL, you can just use the CONCAT_WS() function, which will skip null values when combining column values.

You can test this by running this query:

SELECT CONCAT_WS('', col1, col2) AS title
FROM
(
    SELECT   'apple' AS col1
            ,'juice' AS col2
    UNION
    SELECT   'water'        
            ,'melon'
    UNION
    SELECT   'banana'   
            ,NULL
    UNION
    SELECT   'red'
            ,'berry'
) a

Which gives the desired example result:

applejuice
watermelon
banana
redberry

jtlz2 · Accepted Answer · 2023-10-20 09:22:13Z

0

The + sign for concatenation in TSQL will by default combine string + null to null as an unknown value.

You can do one of two things, you can change this variable for the session which controls what Sql should do with Nulls

http://msdn.microsoft.com/en-us/library/ms176056.aspx

Or you can Coalesce each column to an empty string before concatenating.

COALESCE(Column1, '')

http://msdn.microsoft.com/en-us/library/ms190349.aspx

edited Oct 20, 2023 at 9:22

jtlz2

8,52711 gold badges74 silver badges128 bronze badges

answered Nov 22, 2011 at 21:04

Chris

3,1521 gold badge20 silver badges27 bronze badges

Collectives™ on Stack Overflow

Concatenate with NULL values in SQL

9 Answers 9

Comments

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Comments

2 Comments

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Linked

Related