February 11, 2010 at 12:46 am
I am required to print the address of a customer in a report.
the format will be
address_line_1
address_line_2
address_line_3
city state zip
country
but at time all the address lines will not be specified.. that is some of the line will be null.This causes problems in formating. Empty lines are getting printed in the report.
Following is the sample code
declare @a varchar(4),
@b-2 varchar(4),
@C varchar(5),
@d varchar (4),
@e varchar(20)
set @a = 'aaa'
set @b-2 = NULL
set @C = 'ccc'
set @d = 'ddd'
set @e = 'eee'
select isnull(@a,'')+ char(13) + isnull(@b,'') + char(13)+ isnull(@c,'') +char(13)+ isnull(@d,'') + char(13) +isnull(@e,'')
the result is
-----------------------------------------
aaa
ccc
ddd
eee
But the required result is
-----------------------------------------
aaa
ccc
ddd
eee
(i dont want a empty second line)
your suggestions please.. Should i write a complex if clause or simply go and use the coalesce function.
Or is there any other simply method to achieve the requirement.
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 11, 2010 at 12:50 am
February 12, 2010 at 3:24 am
Hi,
You can avoid all those isnulls with concat_null_yields_null:
declare @a varchar(4),
@b-2 varchar(4),
@C varchar(5),
@d varchar (4),
@e varchar(20)
set @a = 'aaa'
set @b-2 = NULL
set @C = NULL
set @d = Null
set @e = 'EEE'
set concat_null_yields_null off
select replace(replace(@a + char(13) + @b-2 + char(13) + @C + char(13) + @d + char(13) + @e
, char(13) + char(13)
, char(13)
)
, char(13) + char(13)
, char(13)
)
set concat_null_yields_null on
Regards, Iain
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply