Add carriage return to a full address field

Question

Post reply

Add carriage return to a full address field

ams00601

SSC-Addicted

Points: 473
More actions
April 16, 2015 at 4:18 am

#303039

Hi all,
I'm pulling individual address fields into my script, some of which are null.
I would like to add in a carriage return after ONLY those fields that are NOT NULL.
Any help will be appreciated.
Current script is:
SELECT
ISNULL(SAD.HOUSENUMBER,'') + ISNULL(SAD.ADDRESS1,'') + ISNULL(SAD.ADDRESS2,'') + ISNULL(SAD.ADDRESS3,'') + ISNULL(SAD.CITY,'') + ISNULL(SAD.COUNTRY,'') + ISNULL(SAD.ZIP,'') as 'FULL_ADDRESS'
FROM
SFAADDRESS SAD
Thanks and Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Concat NULL yelds NULL:

SELECT

ISNULL(SAD.HOUSENUMBER + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.ADDRESS1 + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.ADDRESS2 + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.ADDRESS3 + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.CITY + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.COUNTRY + CHAR(13) + CHAR(10),'') +

ISNULL(SAD.ZIP + CHAR(13) + CHAR(10),'') as 'FULL_ADDRESS'

FROM

SFAADDRESS SAD

-- Gianluca Sartori

ams00601 SSC-Addicted Points: 473 More actions · Answer 2

ams00601

SSC-Addicted

Points: 473

April 16, 2015 at 4:37 am

#1791274

You are a star 🙂

Thank you very much

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

spaghettidba (4/16/2015)
Concat NULL yelds NULL:
SELECT
ISNULL(SAD.HOUSENUMBER + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.ADDRESS1 + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.ADDRESS2 + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.ADDRESS3 + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.CITY + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.COUNTRY + CHAR(13) + CHAR(10),'') +
ISNULL(SAD.ZIP + CHAR(13) + CHAR(10),'') as 'FULL_ADDRESS'
FROM
SFAADDRESS SAD

I am curious, doesn't one CHAR(13) OR CHAR(10) work? Why both CHAR(13) and CHAR(10)? I have seen other people do this but have never understood why.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

In windows the line separator is CR+LF. In linux it's LF.

CR = CHAR(13)

LF = CHAR(10)

-- Gianluca Sartori

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

spaghettidba (4/16/2015)
In windows the line separator is CR+LF. In linux it's LF.
CR = CHAR(13)
LF = CHAR(10)

Got it. Thanks!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001