Handling Strings

[dpendley]

Hi

SELECT CASE WHEN DX IS NULL THEN EnvelopeName + char(13)
+ ' ' + Address1 + char(13) + Address2 + char(13)
+ ' ' + Address3 + char(13) + ' ' + Address4 + char(13)
+ ' ' + Postcode + ' '
ELSE DX END AS Address, Code
FROM Entities

This works fine but I need to refine it so that returns (char(13)) are only added to rows that are not NULL but im not sure where to start any help you be appreciated.

Thanks in Advance

 

[tlbroadbent]

If I understand your question, all you need to do is add some more CASE statements.

SELECT
CASE
WHEN DX IS NULL THEN
EnvelopeName + char(13) + ' ' +
CASE WHEN Address1 IS NULL
THEN ''
ELSE ADDRESS1 + char(13) + ' ' END +
CASE WHEN Address2 IS NULL
THEN ''
ELSE ADDRESS2 + char(13) + ' ' END +
CASE WHEN Address3 IS NULL
THEN ''
ELSE ADDRESS3 + char(13) + ' ' END +
CASE WHEN Address4 IS NULL
THEN ''
ELSE ADDRESS4 + char(13) + ' ' END +
Postcode + ' '
ELSE DX END AS Address,
Code
FROM Entities
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.