CREATE TABLE #r
(
ID int IDENTITY (1, 1) PRIMARY KEY,
Address_1 varchar (100),
Address_2 varchar (100),
Address_3 varchar (100)
);
go
INSERT INTO #r VALUES ( null, 'Some', 'Avenue' );
INSERT INTO #r VALUES ( '1400', null , 'Avenue' );
INSERT INTO #r VALUES ( '1400', 'Some', 'Avenue' );
INSERT INTO #r VALUES ( '1400 Some Avenue', NULL, NULL );
INSERT INTO #r VALUES ( null, null, 'P.O 130' );
INSERT INTO #r VALUES ( '960A', null, null );
INSERT INTO #r VALUES ( null, 'POB', null );
SELECT * FROM #r;
/*
ID Address_1 Address_2 Address_3
----------- ----------------- --------- ----------
1 NULL Some Avenue
2 1400 NULL Avenue
3 1400 Some Avenue
4 1400 Some Avenue NULL NULL
5 NULL NULL P.O 130
6 960A NULL NULL
7 NULL POB NULL
*/
GO
/*
The number of expressions in the coalesce function will be
(2^n - 1) where n is the number of fields to be concatenated.
This method shows how to concatenate several columns using a
delimiter. The concatenation handles NULLs , empty strings etc.
This case be written as CASE statement too.
*/
SELECT ID, COALESCE(
NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_1, '') + ',' + NULLIF ( address_2, '') ,
NULLIF ( address_1, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_2, '') + ',' + NULLIF ( address_3, '') ,
NULLIF ( address_1, '') ,
NULLIF ( address_2, '') ,
NULLIF ( address_3, '') ,
'' ) as Address
FROM #r;
/*
ID Address
----------- -----------------
1 Some,Avenue
2 1400,Avenue
3 1400,Some,Avenue
4 1400 Some Avenue
5 P.O 130
6 960A
7 POB
*/
GO
DROP TABLE #r;
This page was last updated on May 01, 2006 04:28 PM.