Concatenate Fields When NULLS exsist

Question

Post reply

Concatenate Fields When NULLS exsist

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
August 13, 2010 at 1:55 am

#225829

Good Morning.
Please see the below script as an example of what I am trying to achieve -
SELECT 0 AS RecordStatusID
,LEFT(COALESCE(STOCK.postcode + N'/','') + STOCK.address1 ,30)AS ShortName
,STOCK.ADDRESS1
,STOCK.ADDRESS2
,STOCK.ADDRESS3
,STOCK.address1 + CHAR(13)+ STOCK.address2 + CHAR(13)+ STOCK.address3 AS Address
FROM DataWarehouse.dbo.HEX_STOCK AS STOCK
This brings back 4732 rows, but the first 5 are as below -
RecordStatusID ShortName ADDRESS1 ADDRESS2 ADDRESS3 Address
-------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SE1 9HP/Cromwell Flats Cromwell Flats Redcross Way NULL NULL
0 SE13 7AT/4 Algernon Road 4 Algernon Road NULL NULL NULL
0 SE13 7TD/10 Claybank Grove 10 Claybank Grove NULL NULL NULL
0 SE13 7TD/1 Claybank Grove 1 Claybank Grove NULL NULL NULL
0 SE15 2NG/235 Queens Road 235 Queens Road NULL NULL NULL
Basically Address1, Address2 and Address3 need to be concatenated and imported into another database. They will howevere reside in one datafield called address, so I need to merge the three cells together. Although it is one field I have to place the + CHAR(13) in the script in order for the address to format correctly in the program.
My dilema is that when I add the three together unless a value appear in all three fields Address comes back as a NULL value.
For the first exampe I would want it to call back -
Cromwell Flats Redcross Way.
However because address3 is NULL for this example the whole result is NULL.
Is there anyway that I can add address1 + 2 + 3 by in effect skiping the NULLS?
Thank you

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

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Use COALESCE(Field1,<<Replacement string>>) to avoid the effect of NULLs

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Results still not working I'm afraid -

SELECT 0 AS RecordStatusID

,LEFT(COALESCE(STOCK.postcode + N'/','') + STOCK.address1 ,30)AS ShortName

,STOCK.ADDRESS1

,STOCK.ADDRESS2

,STOCK.ADDRESS3

,STOCK.address1 + CHAR(13)+ STOCK.address2 + CHAR(13)+ STOCK.address3 AS Address

,LEFT(COALESCE(STOCK.address1 + CHAR(13),'') + STOCK.address2 ,255)AS ShortName

FROM DataWarehouse.dbo.HEX_STOCK AS STOCK

Comes back with -

It has appended the first one because address1 and addres2 exsist but as the next examples no address2 appears I get a NULL value again in my result?

Thanks

RecordStatusID ShortName ADDRESS1 ADDRESS2 ADDRESS3 Address ShortName

-------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

0 SE1 9HP/Cromwell Flats Cromwell Flats Redcross Way NULL NULL Cromwell Flats

Redcross Way

0 SE13 7AT/4 Algernon Road 4 Algernon Road NULL NULL NULL NULL

0 SE13 7TD/10 Claybank Grove 10 Claybank Grove NULL NULL NULL NULL

0 SE13 7TD/1 Claybank Grove 1 Claybank Grove NULL NULL NULL NULL

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Use this one:

SELECT 0 AS RecordStatusID

,LEFT(COALESCE(STOCK.postcode + N'/','') + STOCK.address1 ,30)AS ShortName

,STOCK.ADDRESS1

,STOCK.ADDRESS2

,STOCK.ADDRESS3

,ISNULL(STOCK.address1,'') + CHAR(13)+ ISNULL(STOCK.address2,'') + CHAR(13)+ ISNULL(STOCK.address3,'') AS Address

FROM DataWarehouse.dbo.HEX_STOCK AS STOCK

Thanks

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

TSQL Tryer

SSCrazy Eights

Points: 8869

August 13, 2010 at 3:18 am

#1206479

Thank you so so much.

Exactly what I was after.