Solved

SQL concatinate

Posted on 2011-03-24
9
401 Views
Last Modified: 2012-05-11
Hi,
I am trying to create a query that concatenates the columns into a single column delimited with a pipe.  It works, but if one of the columns are null, the answer is null.  How do I make it so my query will treat null as an empty string?

I will have the same issue with date and integer.

SELECT     EmpNo, AssociationName, MemberSince, Position, AssociationName + '|' + CONVERT(varchar, MemberSince) + '|' + Position AS ProfessionalAffiliationsFull
FROM         dbo.EmpAssociations AS e
0
Comment
Question by:jackjohnson44
  • 5
  • 3
9 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 250 total points
ID: 35210871
Rather than using ...

SELECT     EmpNo, AssociationName, MemberSince, Position, AssociationName + '|' + CONVERT(varchar, MemberSince) + '|' + Position AS ProfessionalAffiliationsFull
FROM         dbo.EmpAssociations AS e

Open in new window


use ...

SELECT     EmpNo, AssociationName, MemberSince, Position, ISNULL(AssociationName, '') + '|' + ISNULL(CONVERT(varchar, MemberSince), '') + '|' + ISNULL(Position, '') AS ProfessionalAffiliationsFull
FROM         dbo.EmpAssociations AS e

Open in new window


You can read about the ISNULL function here.

Regards,

Richard.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35210908
Try this way
SELECT EmpNo, AssociationName, MemberSince, Position, 
       ISNULL(AssociationName + '|' , '')+ ISNULL(CONVERT(varchar, MemberSince) + '|', '') + ISNULL(Position, '') AS ProfessionalAffiliationsFull
FROM dbo.EmpAssociations AS e

Open in new window

0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35211021
@ewangoya

It's a pain when you post a duplicate answer isn't it? <grin>
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 32

Expert Comment

by:ewangoya
ID: 35211039

@RQuadling

Please look carefully again. You will see a big difference
You query will put pipes even when the column value returns null
        XXX||YYY  or IXX|YY or IIXXX

Mine will not
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 250 total points
ID: 35211068

@RQuadling

Basically this is what you are doing
ISNULL(AssociationName, '') + '|'
Even if the column is null, you are still returning '|'

Mine on the other hand is
ISNULL(AssociationName + '|' , '')

So if the column is null, I dont put in a | and its much cleaner result
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35211081
No. Please read the question ...

How do I make it so my query will treat null as an empty string?

So NULL => ''



0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35211090
If the output is for a pipe delimited file (like a CSV file), then having a column or three missing is pretty much going to screw up the app/process that uses the file.
0
 

Author Comment

by:jackjohnson44
ID: 35211113
RQuadling, thanks that is what I was looking for.

How do I do the same for dates and integers?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 35212381
cast(isnull(a_numeric_column, 0) as varchar)

cast(isnull(a_date_column, '') as varchar)



0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question