Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 replacing nulls

Author  Topic 

vishalj
Starting Member

32 Posts

Posted - 2006-05-17 : 11:03:04
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).

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 11:13:32
quote:
Originally posted by vishalj

width is null so my result would be 10 x x 12
edition is null i will get in the result Editions: .
@workedyearto is null then i will get 1980, , height


What do u want to c when :

width is null
edition is null
@workedyearto is null

And is it to be happen when any of the above is null or all of those r null ?

Srinika
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-17 : 11:14:14
include it in the string concatenation...

e.g.

coalesce(@height + 'x','')...

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-05-17 : 11:22:54
I would go with:

isnull(@height+'h ', '')+'x'+isnull(@width+'w ', '')+'x'+isnull(@depth+'d ', '')

i.e. delete the blue

Kristen
Go to Top of Page
   

- Advertisement -