replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||thanks but with '+COALESCE('Editions: ' +@.edition, '') since
'Editions' is hard coded even if @.edition is null
i get the Editions in the output
how do i make that null if @.edition is null as well|||My guess is that @.edition is '' which is not the same as NULL.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147881891.316429.181680@.y43g2000cwc.googlegroups.com...
> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>|||Actually no. A NULL value concatenated with any string will result in NULL.
So 'Editions: ' + NULL will yield NULL, instead of 'Editions: '.
The following example shows the result:
declare @.edition varchar(30)
set @.edition = null
select COALESCE('Editions: ' +@.edition, '') -- returns empty string ''
set @.edition = 'foo'
select COALESCE('Editions: ' +@.edition, '') -- returns "Editions: foo"
"VJ" wrote:

> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>