March 28, 2011 at 1:42 pm
Hi all,
I have a simple SQL select statement, where I later pass into an email to be sent to customers... the thing is, most of them do not have an "address 2" line. But if I use ISNULL, then I basically just replace it with nothing and I get a space/linebreak between the address1 line and the city, state, zip... is there a way for me to select/display this field ONY if it is not null OR - can I replace it with a backspace ro reverse carriage return (as silly as that sounds)?
Here is my code:
SELECT
distinct(cust_cd),
ship_to_f_name,
ship_to_l_name,
ship_to_addr1,
isnull(ship_to_addr2, ''),
ship_to_city,
ship_to_st_cd,
ship_to_zip_cd,
ship_to_h_phone,
bill_to_f_name,
bill_to_l_name,
bill_to_addr1,
isnull(bill_to_addr2, ''),
bill_to_city,
bill_to_st_cd,
bill_to_zip_cd,
bill_to_h_phone,
FROM dbo.email_list where convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101);
Thank you in advance,
~D
March 28, 2011 at 1:56 pm
It's usually better to handle that kind of formatting in the application/presentation layer, but if you have to do it in the query, it can look like this:
coalesce(
ship_to_addr1 + char(13) + char(10) + ship_to_addr2,
ship_to_addr1,
ship_to_addr2,
'') as ShippingAddress
Coalesce will pick the first one of those that isn't null, so if you have both, it will give both with a return in between, but if you only have one, it will give the one that's not null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 28, 2011 at 2:30 pm
GSquared (3/28/2011)
It's usually better to handle that kind of formatting in the application/presentation layer, but if you have to do it in the query, it can look like this:
coalesce(ship_to_addr1 + char(13) + char(10) + ship_to_addr2,
ship_to_addr1,
ship_to_addr2,
'') as ShippingAddress
Coalesce will pick the first one of those that isn't null, so if you have both, it will give both with a return in between, but if you only have one, it will give the one that's not null.
Thank you! This works very well, however, it shows addr1 and addr2 on the same line... I see you have char(13) and char(10), but it is still showing on the same line. Not sure why that is... is there something else or....?
Thanks again.
March 28, 2011 at 2:43 pm
I tried running the query in Text mode and it looks like it is on the same line there as well...
March 29, 2011 at 6:07 am
Try this instead:
ship_to_addr1 +'
' + ship_to_addr2
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2011 at 6:58 am
GSquared (3/29/2011)
Try this instead:
ship_to_addr1 +'' + ship_to_addr2
Still on the same line...
I may just keep it that way, although I really would like it to be on a new line....
March 30, 2011 at 4:56 am
CR & LF will not show in Query Analyzer (I've had to do some fancy footwork to remove CR & LF from my data), but if you print it will be on a new line. Here's an example:
Print coalesce( 'A' + char(13) + char(10) + 'B', 'A', 'B', '')
March 30, 2011 at 8:57 am
PurpleLady (3/30/2011)
CR & LF will not show in Query Analyzer (I've had to do some fancy footwork to remove CR & LF from my data), but if you print it will be on a new line. Here's an example:Print coalesce( 'A' + char(13) + char(10) + 'B', 'A', 'B', '')
It doesn't show when I load the page on our website. It stays on the same line...
March 30, 2011 at 12:32 pm
Then try
Print coalesce( 'A' + ' ' + ' ' + 'B', 'A', 'B', '')
You need to use values that html will convert to CR/LF. Not sure what they are tho.
March 30, 2011 at 12:55 pm
Have you tried changing the char(13) + char(10) to < br >? (Without the spaces, of course. Forum software wants to consume the tag if I type it correctly.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2011 at 2:13 pm
GSquared (3/30/2011)
Have you tried changing the char(13) + char(10) to < br >? (Without the spaces, of course. Forum software wants to consume the tag if I type it correctly.)
It is adding a blank space again... very strange.
This is what I have now:
coalesce(
ship_to_addr1 + '<br/>' + ship_to_addr2,
ship_to_addr1,
ship_to_addr2,
'') as ShippingAddress
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply