Parse Address Block via View into separate columns

Transact-SQL
1

We have three tables.
We are performing a Left Outer Join to combine three tables
Table 1: Order_Sales_Order
Table 2: Customer_Shipto
Table 3: Order_Shipment

Our current view comes back with the following: (All three tables are joined)

OrderNumber, ShipToNumber, CustomerCode, CustomerName, Address, City, State, Zip, Phone, Email, Fax,

The Issue we are having is with the Address Block: Address (this is one address block)

Attn: John Doe (CHAR(13)) 123 Somewhere Street (CHAR(13)) Suite 10345, #6

Thankfully City, State, Zip are in their own fields.

The Address Block can have 0 lines, 1 line, some have 2 lines, some have 3 lines and some have 4 lines.
We probably even have a few with 5 or 6 carriage return lines..
Each line is seperated with a ASCII CR carriage return char(13) in database

We need to parse the AddressBlock to seperate columns in the existing SQL view

OrderNumber, ShipToNumber, CustomerCode, CustomerName, Address, Address 1, Address 2, Address 3, Address 4, Address 5, Address 6, Address..(Etc.), City, State, Zip, Phone, Email, Fax

We would like to perform this parse inside the existing sql view

Ideas on how to accomplish this? We have searched google but are coming up short.

We would also have the ability to also use a UDF function. We could call that.

We need some help skinning this cat. Any help is greatly appreciated.

*** We are using a third party solution for our software. We do not have the ability to split AddressBlock into a normalized database structure using Attn, Address1, Address2, Address3, City, State, Zip. Adress blocks are painful for teams to parse. Sometimes it has to be done.

2

An Address consists officially, per the USPS, of five lines. It sounds like one of those lines is taken up with the distinct fields City, State and Zip. One approach would be to populate the other lines by taking SUBSTRINGs of your Address field based on the location of each Carriage Return. This gets ugly quickly but would be conceptually straightforward. An alternative approach would be to use a splitter function, such as here http://www.sqlservercentral.com/articles/Tally+Table/72993/, using Carriage Return as the delimiter and then Pivot the data to fill your four fields.

3

You can split the AddressBlock column into 6 columns, based on CHAR(13) separator, with this brute-force SubString() approach. I don't know if it performs worse/better than an CROSS APPLY type approach (below)

SELECT
  [ADDRESS_1] = substring(AddressBlock + CHAR(13), 0 + 1, charindex(CHAR(13), AddressBlock + CHAR(13), 0 + 1) - 0 - 1 )
, [ADDRESS_2] = substring(AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1, charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) -  charindex(CHAR(13), AddressBlock + CHAR(13)) - 1 )
, [ADDRESS_3] = substring(AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1, charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) -  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) - 1 )
, [ADDRESS_4] = substring(AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1, charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) -  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) - 1 )
, [ADDRESS_5] = substring(AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) + 1, charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) + 1) -  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) - 1 )
, [ADDRESS_6] = substring(AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) + 1) + 1, charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) + 1) + 1) -  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13),  charindex(CHAR(13), AddressBlock + CHAR(13)) + 1) + 1) + 1) + 1) - 1 )
FROM	dbo.Order_Shipment

CROSS APPLY would be something like this:


SELECT
	[ADDRESS_1] = NullIf(LEFT(AddressBlock, CHARINDEX(CHAR(13), AddressBlock+CHAR(13))-1), ''),
	[ADDRESS_2] = NullIf(LEFT(C2, CHARINDEX(CHAR(13), C2+CHAR(13))-1), ''),
	[ADDRESS_3] = NullIf(LEFT(C3, CHARINDEX(CHAR(13), C3+CHAR(13))-1), ''),
	[ADDRESS_4] = NullIf(LEFT(C4, CHARINDEX(CHAR(13), C4+CHAR(13))-1), ''),
	[ADDRESS_5] = NullIf(LEFT(C5, CHARINDEX(CHAR(13), C5+CHAR(13))-1), ''),
	[ADDRESS_6] = NullIf(LEFT(C6, CHARINDEX(CHAR(13), C6+CHAR(13))-1), '')
FROM	dbo.Order_Shipment
	CROSS APPLY (SELECT [C2]=STUFF(AddressBlock, 1, CHARINDEX(CHAR(13), AddressBlock+CHAR(13)) ,'')) AS P2
	CROSS APPLY (SELECT [C3]=STUFF(C2, 1, CHARINDEX(CHAR(13), C2+CHAR(13)) ,'')) AS P3
	CROSS APPLY (SELECT [C4]=STUFF(C3, 1, CHARINDEX(CHAR(13), C3+CHAR(13)) ,'')) AS P4
	CROSS APPLY (SELECT [C5]=STUFF(C4, 1, CHARINDEX(CHAR(13), C4+CHAR(13)) ,'')) AS P5
	CROSS APPLY (SELECT [C6]=STUFF(C5, 1, CHARINDEX(CHAR(13), C5+CHAR(13)) ,'')) AS P6
4

This table valued split function might fit the bill:

/*  County of Riverside DPSS IT Services
	Data Management
	Created:	11/2/2010
	By:			Joe Torre
	
	Usage:
	DECLARE 
	   @string varchar(1000)='The, quick, brown, fox, jumped, over, the, lazy, dog', @del varchar(8)=', ';
	SELECT * FROM dbo.Split(@string, @del);	
*/
CREATE FUNCTION [dbo].[Split]
   (
    @string varchar(2047)
   ,@delimiter varchar(8) = ' '
   )
RETURNS @ret table
   (
    Id int IDENTITY
           PRIMARY KEY
   ,Word varchar(100)
   )
AS 
   BEGIN
	   
	   DECLARE @Pos table(POSITION int);
	   
	   WITH Positions(Position) AS
	      (
	         SELECT 1
	         UNION ALL
	         SELECT Position + 1
	         FROM Positions WHERE Position<2048
	      ) 
     
      INSERT @ret
         ( Word
         )
      SELECT
         LTrim(Substring(string, Position,
                         CharIndex(@delimiter, string + @delimiter, Position)
                         - Position)) AS string
      FROM
         ( SELECT
               @string
         ) t ( string )
      CROSS JOIN Positions
      WHERE
         Substring(@delimiter + string, Position, 1) = @delimiter
         AND [Position] < Len(string) + 1 
	   OPTION (MAXRECURSION 2047) ;
	   
      RETURN
	
   END

GO