SQL concatinate

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
jackjohnson44Asked:
Who is Participating?
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
Ephraim WangoyaCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
@ewangoya

It's a pain when you post a duplicate answer isn't it? <grin>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ephraim WangoyaCommented:

@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
 
Ephraim WangoyaCommented:

@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
 
Richard QuadlingSenior Software DeveloperCommented:
No. Please read the question ...

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

So NULL => ''



0
 
Richard QuadlingSenior Software DeveloperCommented:
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
 
jackjohnson44Author Commented:
RQuadling, thanks that is what I was looking for.

How do I do the same for dates and integers?
0
 
Richard QuadlingSenior Software DeveloperCommented:
cast(isnull(a_numeric_column, 0) as varchar)

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



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.