TSQL to transform Address into a Mailing Address - SQL Server 2005

I would like to transform an Address (Line1, Line2, Line3, City, State, ZIP) into a Mailing Address (Addr1, Addr2, Addr3, Addr4) that has no blank lines and the City, State and ZIP are concatenated together on one line. Having a function do this would be very nice.

i.e.

Line1=

Line2=123 Somewhere

Line3=

City=Detroit

State=MI

Zip=48000

Here is the table stucture for the incoming address:

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL DROP TABLE #Employee
CREATE TABLE #Em开发者_StackOverflow中文版ployee (Line1 VARCHAR(30), Line2 VARCHAR(30), Line3 VARCHAR(30), 
  City VARCHAR(17), State VARCHAR(2), ZIP VARCHAR(10)) 
GO 

INSERT #Employee VALUES ('', '123 Somewhere', '', 'Detroit', 'MI', '48000') 

SELECT * FROM #Employee  

The resulting Mailing Address

Addr1=123 Somewhere

Addr2=Detroit MI 48000

Addr3=

Addr4=

or one field with cr character

Addr=

123 Somewhere cr

Detroit MI 48000 cr

cr

cr

A function would be nice to return Addr1, Addr2, Addr3 and Addr4 or just Addr with .

SqueezeAddress(Line1, Line2, Line3, City, State, ZIP)

Then SqueezeAddress would return Addr1, Addr2, Addr3, Addr4

or

Addr with cr

All the Addr1-4 lines would be VARCHAR (40) or if one field is used Addr VARCHAR (200)

Per Phil's request in the comments below, here is the current logic that is being used (Many fields were removed to make it easier to read):

SELECT Line1, Line2, Line3, 
    ISNULL(LTRIM(RTRIM(ADDR.City)) + ', ','')  + ISNULL(ADDR.RegionCode,'') 
    + ' ' + ISNULL(ADDR.PostalCode,'') AS Line4,

  UPDATE #tmpBilling
  SET Line1 = Line2, Line2 = NULL
  WHERE ISNULL(Line1, '') = ''
    AND ISNULL(Line2, '') <> ''

  UPDATE #tmpBilling
  SET Line2 = Line3, Line3 = NULL
  WHERE ISNULL(Line2, '') = ''
    AND ISNULL(Line3, '') <> ''

  UPDATE #tmpBilling
  SET Line2 = Line4, Line4 = NULL
  WHERE ISNULL(Line2, '') = ''
    AND ISNULL(Line4, '') <> ''

  UPDATE #tmpBilling
  SET Line3 = Line4, Line4 = NULL
  WHERE ISNULL(Line3, '') = ''
    AND ISNULL(Line2, '') <> ''

---

I may be missing something here, but if this is just simple string concatenation, then this would work...

Set up testing data (I added a few more samples)

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL DROP TABLE #Employee 
CREATE TABLE #Employee (Line1 VARCHAR(30), Line2 VARCHAR(30), Line3 VARCHAR(30),  
  City VARCHAR(17), State VARCHAR(2), ZIP VARCHAR(10))  
GO  

INSERT #Employee VALUES ('', '123 Somewhere', '', 'Detroit', 'MI', '48001')  
INSERT #Employee VALUES ('123 Somewhere', 'Suite 500', '', 'Detroit', 'MI', '48002')  
INSERT #Employee VALUES ('123 Somewhere', 'Suite 500', 'attn: JP', 'Detroit', 'MI', '48003')  

SELECT * FROM #Employee 

From here, all you have to do is stitch the strings together. This version presumes that you have both nulls and empty strings to factor out.

SELECT
    isnull(nullif(Line1, '') + char(13) + char(10), '')
  + isnull(nullif(Line2, '') + char(13) + char(10), '')
  + isnull(nullif(Line3, '') + char(13) + char(10), '')
  + City + ' ' + State + ' ' + ZIP
  + char(13) + char(10) + '------------------------------'
 from #Employee

Wrap that into a function:

CREATE FUNCTION dbo.SqueezeAddress
 (
   @Line1  varchar(30)
  ,@Line2  varchar(30)
  ,@Line3  varchar(30)
  ,@City   varchar(17)
  ,@State    varchar(2)
  ,@Zip    varchar(10)
 )
RETURNS varchar(200)
AS
 BEGIN
    RETURN isnull(nullif(@Line1, '') + char(13) + char(10), '')
            + isnull(nullif(@Line2, '') + char(13) + char(10), '')
            + isnull(nullif(@Line3, '') + char(13) + char(10), '')
            + @City + ' ' + @State + ' ' + @ZIP
            + char(13) + char(10) + '------------------------------'
 END
GO

Lastly, put the function in the query:

SELECT dbo.SqueezeAddress(Line1, Line2, Line3, City, State, Zip)
 from #Employee

---

More straightforward (and easier to debug, IMHO):

-------------------------------------------------------------
-- assumptions:
-- 
-- * nullable fields never contain an nil (empty) string.
--   every nullable column will contain either a proper value
--   or NULL.
--
-- * zipcode is a 5- or 9-digit USPS zip code, without a dash.
--   Addresses lacking a zipcode will be NULL.
--------------------------------------------------------------
drop table dbo.address
go
create table dbo.address
(
  id int not null identity(1,1) primary key clustered ,
  line1   varchar(100) null ,
  line2   varchar(100) null ,
  line3   varchar(100) null ,
  city    varchar(100) null ,
  state   varchar(2)   null ,
  zipcode varchar(9)   null ,
)
go
-----------------------------------------------------------------------
-- create a work table and rotate the source table such that
-- the work table contains 1 row for each non-null row for each address
-----------------------------------------------------------------------
drop table #addr
go
create table #addr
(
  id      int          not null , -- pk.1
  line_no int          not null , -- pk.2
  value   varchar(100) not null ,

  primary key clustered ( id , line_no ) ,

)
go

insert #addr ( id , line_no , value )
select addr.id , addr.line_no , addr.value
from ( select id      = t.id ,
       line_no = row_number() over ( partition by t.id order by t.seq ) ,
       value   = t.value
       from ( select id    = id ,
                     seq   = 1  ,
                     value = line1
              from dbo.address where line1 is not null
              UNION
              select id    = id ,
                     seq   = 2  ,
                     value = line2
              from dbo.address where line2 is not null
              UNION
              select id    = id ,
                     seq   = 3  ,
                     value = line3
              from dbo.address where line3 is not null
              UNION
              select id    = id ,
                     seq   = 4  ,
                     value = ltrim(rtrim(
                                 coalesce( city    , '' )
                               + case when city is not null and state is not null then ', ' else '' end
                               + coalesce( state   , '' )
                               + case when ( city is not null or state is not null ) and zipcode is not null then ' ' else '' end
                               + coalesce( left(zipcode,5) , '' )
                               + case when len(zipcode) = 9 then '-' + right(zipcode,4) else '' end
                               ))
              from dbo.address
              where city    is not null
                 OR state   is not null
                 OR zipcode is not null
            ) t
     ) addr

---------------------------------------------------------------------
-- finally, do another table rotation to build the desired result set
---------------------------------------------------------------------
select id    = addr.id    ,
       line1 = line1.value ,
       line2 = line2.value ,
       line3 = line3.value ,
       line4 = line4.value
from      #addr addr
left join #addr line1 on line1.id = addr.id and line1.line_no = 1
left join #addr line2 on line2.id = addr.id and line2.line_no = 2
left join #addr line3 on line3.id = addr.id and line3.line_no = 3
left join #addr line4 on line4.id = addr.id and line4.line_no = 4
order by addr.id

---

Assuming that empty values are actually NULL and not empty strings and that City, State and Zip are requried:

;With AddressValues As
    (
    Select PK, Line1 As LineValue, 1 As LinePos
    From AddressTable
    Union All
    Select PK, Line2, 2
    From AddressTable
    Union All
    Select PK, Line3, 3
    From AddressTable
    Union All
    Select PK, [City] + ', ' + [State] + '  ' + [Zip], 4
    From AddressTable
    )
    , OrderedValues As
    (
    Select PK, LineValue
        , Row_Number() Over( Partition By PK Order By LinePos ) As Num
    From AddressValues
    Where LineValue Is Not Null
    )
    , ValuesAsColumns As
    (
    Select PK
        , Case When Num = 1 Then LineValue End As Line1
        , Case When Num = 2 Then LineValue End As Line2
        , Case When Num = 3 Then LineValue End As Line3
        , Case When Num = 4 Then LineValue End As Line4
    From OrderedValues
    Group By PK
    )
Update #tmpBilling
Set Line1 = VC.Line1
    , Line2 = VC.Line2
    , Line3 = VC.Line3
    , Line4 = VC.Line4
From #tmpBilling As B
    Join ValuesAsColumns As VC
        On VC.PK = B.PK

EDIT

Here is the same result in the form of a function:

CREATE FUNCTION dbo.SqueezeAddress
(
    @Line1 varchar(50)
    , @Line2 varchar(50)
    , @Line3 varchar(50)
    , @City varchar(50)
    , @State varchar(50)
    , @Zip varchar(50)
    , @LineNumToReturn int

)
RETURNS varchar(50)
AS
BEGIN

Declare @Result varchar(50);

With AddressValues As
    (
    Select @Line1 As LineValue, 1 As LinePos
    Union All
    Select @Line2, 2
    Union All
    Select @Line3, 3
    Union All
    Select @City + ', ' + @State + '  ' + @Zip, 4
    )
    , OrderedValues As
    (
    Select LineValue
        , Row_Number() Over( Order By LinePos ) As Num
    From AddressValues
    Where LineValue Is Not Null
    )
Select @Result = LineValue
From OrderedValues
Where Num = @LineNumToReturn

Return @Result  

END
GO

Select dbo.SqueezeAddress(null, '123 Main St', null, 'Detroit', 'MI', '12345', 1)
, dbo.SqueezeAddress(null, '123 Main St', null, 'Detroit', 'MI', '12345', 2)
, dbo.SqueezeAddress(null, '123 Main St', null, 'Detroit', 'MI', '12345', 3)
, dbo.SqueezeAddress(null, '123 Main St', null, 'Detroit', 'MI', '12345', 4)

)