asked on

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

ASKER CERTIFIED SOLUTION

Richard Quadling

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ephraim Wangoya

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

Richard Quadling

@ewangoya

It's a pain when you post a duplicate answer isn't it? <grin>

Ephraim Wangoya

@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

SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Quadling

No. Please read the question ...

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

So NULL => ''

Richard Quadling

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.

jackjohnson44

ASKER

RQuadling, thanks that is what I was looking for.

How do I do the same for dates and integers?

Richard Quadling

cast(isnull(a_numeric_column, 0) as varchar)

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