concatenate issue -- sql server new users

sql server new users : concatenate issue

Richard
10/6/2005 12:00:00 AM

Hi

I created a pass through query which concatenate a few fields. When one of
the fields is null, the whole string doesn't show. How do I over come this
issue.

the syntax

SELECT Company.CompanyId, Company.COMPANY, ([Floor]+' ') + ([building]+' ')
& ([street No]+' ') & [street] AS [MAILING ADD]
FROM Company

Many thanks in advance
Richard

Richard
10/6/2005 12:00:00 AM

Hello

I managed to overcome the issue. But if the first field is null, the result
will have a space in front. How do I overcome this.

In Access, if we do this, (FloorNo + " ") & building. There won't be any
space in front of building.

Thanks in advance
Richard

[quoted text, click to view]

Stu
10/6/2005 3:15:25 AM

Have you tried:

SELECT Company.CompanyId, Company.COMPANY, COALESCE([Floor]+' ', '') +
COALESCE ([building]+' ', '')
& ([street No]+' ') & [street] AS [MAILING ADD]
FROM Company

Untested.

Stu

Stu
10/6/2005 3:17:18 AM

whoops:

I meant:

SELECT Company.CompanyId, Company.COMPANY, COALESCE([Floor]+' ', '') +
COALESCE ([building]+' ', '')
+ ([street No]+' ') + [street] AS [MAILING ADD]
FROM Company

Stu

Richard
10/7/2005 12:00:00 AM

Hi Stu

Thanks for the tip. Works perfectly.

Richard

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn