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!  :-))

Show quoteHide quote

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
Show quoteHide quote

SQL Server 2005 Linked Server IDENTITY_INSERT
how can I tell if a query is running
SQL Server high utilization
Case Statement Woes
Difference between Index & Statistics
CPU usage and troubleshoot (sp_who2, profiling)
SQL Server 2005 Sgent will not start - Service Time out error
How to read SQL file ?
Outer Join Problem - hardest query ever?
Could not allocate space for object 'xxx' in database 'abc' becaus