SQL Server Performance Forum – Threads Archive
Replacing some values if NULL
I have a string like thisselect @feed5 = @name+’, ‘[email protected]_name +’, ‘+isnull( @workedyearfrom, ”)+’ ‘+ isnull(@workedyearto, ”)+’, ‘+ isnull(@height, ”)+’x’+isnull(@width, ”)+’x’+isnull(@depth, ”)+’ ‘+ @measuretype+’ ‘+’Editions: ‘+’ ‘ [email protected]+.+
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).
Instead of the ISNULL function, use a CASE construct: …………………………….
+ (CASE WHEN (@name IS NULL)
THEN ‘………..’
ELSE ‘…………..’ END)
+ ………………………….. You can extend this to any logical expression:
CASE WHEN (@name IS NULL OR @name = ”) THEN ……………………………. To keep things tidy in your sript, you should put each + sign at the start of a new line in your script. This way it will be easier to focus on each part of the concatenation.
I think ISNULL will work correctly in your case as you want replace NULL value by ”
Use ISNULL function for all the variables used Madhivanan Failing to plan is Planning to fail
Hi Vishal,
As you know Null+’any string’=NULL
so consider the same logic and build your string.
Check this: declare @feed5 varchar(50),@name varchar(20),@work_name varchar(20),
@workedyearfrom varchar(20),@workedyearto varchar(20),@height varchar(20),
@width varchar(20),@depth varchar(20),@measuretype varchar(20),@edition varchar(20) select @name=’ ranjit’,@work_name =’BT’,@measuretype=’SQL’,@edition=’First’,[email protected]=’50’,
@height=’20’,@workedyearfrom=’1980′–,@depth=’70’ select @feed5 = @name+’, ‘[email protected]_name +’, ‘+isnull( @workedyearfrom+’ ‘, ”)+
isnull(@workedyearto+’, ‘, ”)+ isnull(@height, ”)+isnull(‘x’[email protected]+’x’, ”)+
isnull(‘x’[email protected], ”)+’ ‘+ @measuretype+’ ‘+’Editions: ‘ [email protected]+’.’ select @feed5 This way you can get rid of unwanted ‘X , ‘
]]>