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).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>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).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:
> 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).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>