Concatenating strings

I have a table with seven different descriptor fields, and at times it's
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:

> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.