CR & LF Problems

I'm trying to concatenate three address fields into one for a SELECT
statement. Below is that part:
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address,
But the results do not have the CR and LF in it. What am I doing
wrong?I don't see a problem here:
create table #tmp (addr1 varchar(20), addr2 varchar(20), addr3 varchar(20))
insert #tmp values ('line 1 field 1', 'line 1 field 2', 'line 1 field 3')
insert #tmp values ('line 2 field 1', 'line 2 field 2', NULL)
insert #tmp values (NULL, 'line 3 field 2', NULL)
SELECT
CASE WHEN ADDR1 IS NULL
THEN ''
ELSE ADDR1 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR2 IS NULL
THEN ''
ELSE ADDR2 + CHAR(13) + CHAR(10)
END +
CASE WHEN ADDR3 IS NULL
THEN ''
ELSE ADDR3
END AS Address
FROM #tmp
output:
Address
---
line 1 field 1
line 1 field 2
line 1 field 3
line 2 field 1
line 2 field 2
line 3 field 2
P.S. It sure woulda been nice if you had taken the time to construct the
demo code I did! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>|||Paul,
If you run this query to return results in a grid in Query Analyzer or the
Management Studio Query editor (depending on version of SQL) the results
appear in a single cell. But if your results return as text you will see
the line breaks.
RLF
"Paul" <pwh777@.hotmail.com> wrote in message
news:1178224810.009904.225630@.h2g2000hsg.googlegroups.com...
> I'm trying to concatenate three address fields into one for a SELECT
> statement. Below is that part:
> CASE WHEN ADDR1 IS NULL
> THEN ''
> ELSE ADDR1 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR2 IS NULL
> THEN ''
> ELSE ADDR2 + CHAR(13) + CHAR(10)
> END +
> CASE WHEN ADDR3 IS NULL
> THEN ''
> ELSE ADDR3
> END AS Address,
> But the results do not have the CR and LF in it. What am I doing
> wrong?
>