Avoiding NULL values - CONCATENATION

Question

I have the following SQL query (SQL Server 2005):

Select TOP 1000 col1,
LTRIM(RTRIM(cast(col2 as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(col3 as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(col4 as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(col5 as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(col6 as varchar))))
from mytable

But I'm having some problems because these columns are all nullable and I cannot have NULL values in this concatenation. What's the nicest way to avoid NULL values in this situation?

Thanks in advance!

Bridge · Accepted Answer · 2013-09-16 14:22:01Z

3

Wrap the fields in ISNULL()

Select TOP 1000 col1,
LTRIM(RTRIM(cast(ISNULL(col2,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col3,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col4,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col5,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col6,'') as varchar))))
from mytable

Or COALESCE()

Select TOP 1000 col1,
LTRIM(RTRIM(cast(COALESCE(col2,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(COALESCE(col3,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(COALESCE(col4,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(COALESCE(col5,'') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(COALESCE(col6,'') as varchar))))
from mytable

If using SQL Server 2012 it's even easier thanks to CONCAT() (you can replace SPACE(2) with ' | ' above too):

Select TOP 1000 col1
          ,CONCAT(LTRIM(RTRIM(cast(col2 as varchar)))
                ,' | ', LTRIM(RTRIM(col3)) 
                ,' | ', LTRIM(RTRIM(col4)) 
                ,' | ', LTRIM(RTRIM(col5)) 
                ,' | ', LTRIM(RTRIM(col6)))
from mytable

edited Sep 16, 2013 at 14:22

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

answered Sep 16, 2013 at 13:56

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

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

6 Comments

D. Caan Over a year ago

How would the query look like if I use COALESCE()?

dnoeth Over a year ago

just replace ISNULL with COALESCE :-)

D. Caan Over a year ago

:-) haha thanks. I didn't know the syntax was EXACTLY the same... :-)

roman Over a year ago

it's COALESCE not COALEASCE

Hart CO Over a year ago

Typo's abound in the morning!

|

Sonam · Accepted Answer · 2013-09-16 13:55:35Z

1

You can have some other value when there is a NUll, through ISNULL function. Here is a sample query:

Select TOP 1000 col1,
LTRIM(RTRIM(cast(ISNULL(col2,'SOMEVALUE') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col3,'SOMEVALUE') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col4,'SOMEVALUE') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col5,'SOMEVALUE') as varchar))) 
+ SPACE(2) + '|' + SPACE(2) + LTRIM(RTRIM(cast(ISNULL(col6,'SOMEVALUE') as varchar))))
from mytable

answered Sep 16, 2013 at 13:55

Sonam

3,4861 gold badge14 silver badges25 bronze badges

Comments

Bridge · Accepted Answer · 2013-09-16 14:23:26Z

1

If this is a non-critical system and you can't be bothered to COALESCE or ISNULL every column, you could use the CONCAT_NULL_YIELDS_NULL server variable which treats concatenating null values as empty strings:

SELECT 'Hi ' + NULL

Would yield null.

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT 'Hi' + NULL

SET CONCAT_NULL_YIELDS_NULL ON

Produces: "Hi".

Documentation is here - be aware that they are going to remove this feature in future however.

Otherwise, just turn the setting off, and run your original query.

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

edited Sep 16, 2013 at 14:23

answered Sep 16, 2013 at 13:57

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

5 Comments

Hart CO Over a year ago

Not sure why you got downvoted, your answer is consistent with what I've read. In 2012 they've changed this, and now CONCAT() can be used, but since OP didn't specify version your answer seems fine to me.

gvee Over a year ago

Why suggest a method that's been deprecated?

Bridge Over a year ago

@gvee As I said: If this is a non-critical system and you can't be bothered to COALESCE or ISNULL every column. It might be this is a quick fix for something, and so this is less work.

gvee Over a year ago

The note marked "Important" in the documentation kind of answers it all. I don't care if it's non-critical; it's really bad practice. When it's removed from a future version would the solution then be "set the compatibility of your database to 100 then use this option"?

Bridge Over a year ago

@gvee No of course not - if this admittedly temporary solution were migrated further than SQL Server 2012, it would be better to use one of the other answers here - e.g. CONCAT() from Goat's answer above (I gave that answer an upvote).

roman · Accepted Answer · 2013-09-16 14:00:52Z

0

If you want to properly concatenate your values and don't want to use some placeholder for nulls, use isnull (or coalesce) + stuff:

select top 1000
    col1,
    stuff(
       isnull('  |  ' + cast(col2 as varchar), '') +
       isnull('  |  ' + cast(col3 as varchar), '') +
       isnull('  |  ' + cast(col4 as varchar), '') +
       isnull('  |  ' + cast(col5 as varchar), '')
    , 1, 3, '')
from mytable

sql fiddle demo

answered Sep 16, 2013 at 14:00

roman

118k30 gold badges205 silver badges209 bronze badges

2 Comments

Hart CO Over a year ago

Not sure why the downvote, if looking for a consistent number of delimiters then obviously this wouldn't do, but OP doesn't specify, so this answer adds value.

roman Over a year ago

@GoatCO Actually in my expirience it's harder to come up with solution with insonsistent number of delimiters. It's very easy just to put isnull(.., smth), but this one is readable solution for proper concatenation with nulls. Thanks for upvote if it's you :) Let's wait fo OP, for me it's looking that he needs my solution :)

Collectives™ on Stack Overflow

Avoiding NULL values - CONCATENATION

4 Answers 4

6 Comments

Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related