CODEWITHSUNDEEP
sqlsql-server
D. Caan
D. Caan

Reputation: 2019

Avoiding NULL values - CONCATENATION

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!

Upvotes: 0

Views: 980

Answers (4)

Bridge
Bridge

Reputation: 30651

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

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 3

roman
roman

Reputation: 117380

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

Upvotes: 0

Sonam
Sonam

Reputation: 3466

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

Upvotes: 1

Related Questions