|
Home > Archive > MS SQL Server New Users > October 2005 > concatenate issue
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Richard 2005-10-27, 7:55 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 2005-10-27, 7:55 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
Richard" <richardbee@hotmail.com> wrote in message
news:ebhFnflyFHA.916@TK2MSFTNGP10.phx.gbl...
> 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
>
>
| |
|
| Have you tried:
SELECT Company.CompanyId, Company.COMPANY, COALESCE([Floor]+' ', '') +
COALESCE ([building]+' ', '')
& ([street No]+' ') & [street] AS [MAILING ADD]
FROM Company
Untested.
Stu
| |
|
| whoops:
I meant:
SELECT Company.CompanyId, Company.COMPANY, COALESCE([Floor]+' ', '') +
COALESCE ([building]+' ', '')
+ ([street No]+' ') + [street] AS [MAILING ADD]
FROM Company
Stu
| |
| Richard 2005-10-27, 7:55 am |
| Hi Stu
Thanks for the tip. Works perfectly.
Richard
"Stu" <stuart.ainsworth@gmail.com> wrote in message
news:1128593838.373293.215020@z14g2000cwz.googlegroups.com...
> whoops:
>
> I meant:
>
> SELECT Company.CompanyId, Company.COMPANY, COALESCE([Floor]+' ', '') +
> COALESCE ([building]+' ', '')
> + ([street No]+' ') + [street] AS [MAILING ADD]
> FROM Company
>
> Stu
>
|
|
|
|
|