Split Address field

Question

I have a address column that contains street address along with the PO Box no.I would like to extract street address and postcode into a separate column, how can I do that?

Sample Data

     1 ABC CDE PO BOX 650
       15 N. MAIN STREET P.O. BOX 009
       (ABC)PO BOX 5909 
        1 TAKEWAY PLAZA          
       PO BOX 146012  Parkway STREET

DESIRED OUTPUT:-

STREET ADDRESS               ADDRESS2


  1 ABC CDE                    P.O. BOX 650
  15 N. MAIN STREET            P.O. BOX 009
  ABC                          P.O. BOX 5909
  1 TAKEWAY PLAZA
  Parkway STREET               P.O. BOX 146012

https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql — Joe Phillips, May 08 '17 at 20:00
https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql — Joe Phillips, May 08 '17 at 20:00
Please make some sort of attempt. See above comments for documentation — Joe Phillips, May 08 '17 at 20:03
I tried using SELECT PATINDEX('%[0-9]%', ADDRESS_STREET) as [Number], ADDRESS_STREET FROM ABC . But here the problem is that even building number or unwanted number is seen in the output — mehtat_90, May 08 '17 at 20:06
Syntax isn't your issue then... you are looking for a rule engine. I don't think you want to try and do this in SQL. Can you do it externally instead? — Joe Phillips, May 08 '17 at 20:09

score 4 · Accepted Answer · edited May 23 '17 at 11:47

Parsing addresses can be a slippery slope. That said, you seem to be keying off of PO Box, so that makes it a little more managable,

That said, you may want to consider using the GOOGLE API to normaize your address Address standardization within a database

This will produce the desired results by normalizing and cleaning the string. Then it is a simple matter of parsing the string, and then reconstructing it via XML and Stuff().

There are many parse/split functions available, I did provide mine below

Example

Declare @YourTable table (address varchar(250))
Insert Into @YourTable values
('1 ABC CDE PO BOX 650'),
('15 N. MAIN STREET P.O. BOX 009'),
('(ABC)PO BOX 5909'),
('1 TAKEWAY PLAZA'),          
('PO BOX 146012  Parkway STREET')

Select A.*
      ,C.*
 From  @YourTable A
 Cross Apply (
               Select CleanString = replace(replace(replace(replace(replace(replace(replace(A.Address,'  ',' '),'P. O','P.O'),'P.','P'),'O. ','O'),'O BOX','OBOX'),'BOX ','BOX'),'POBOX',' POBOX')+' '
             ) B
 Outer Apply (
                Select Address1 = Stuff((Select ' ' +RetVal 
                                         From  [dbo].[udf-Str-Parse](B.CleanString,' ')
                                         Where RetVal Not Like 'POBOX%'
                                         For XML Path ('')),1,1,'')
                      ,Address2 = (Select replace(RetVal,'POBOX','P.O. Box ') From  [dbo].[udf-Str-Parse](B.CleanString,' ') Where RetVal Like 'POBOX%')
             ) C

Returns

address                         Address1            Address2
1 ABC CDE PO BOX 650            1 ABC CDE           P.O. Box 650
15 N. MAIN STREET P.O. BOX 009  15 N. MAIN STREET   P.O. Box 009
(ABC)PO BOX 5909                (ABC)               P.O. Box 5909
1 TAKEWAY PLAZA                 1 TAKEWAY PLAZA     NULL
PO BOX 146012  Parkway STREET   Parkway STREET      P.O. Box 146012

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

This is awesome, way better than what I was working on. Could be even better if you squeezed out double spaces in sub-query B (and perhaps also handled `P. ` (i.e. a trailing space on that bit). That would prevent the P.O. Box getting "split". — Steve Lovell, May 08 '17 at 21:17
@SteveLovell Thank you. You may notice the double spaces are eliminated by the parse since the delimiter is a space — John Cappelletti, May 08 '17 at 21:20
Yes, no double spaces in the final output, but their presence in B can lead to oddities. Add `15 N. MAIN STREET P.O. BOX 009` and `1 ABC CDE P. O. BOX 650` to the data set to see what I mean. — Steve Lovell, May 08 '17 at 21:22
Darn, HTML formatting removed the double space in my first example, it's meant to be before the `009`. — Steve Lovell, May 08 '17 at 21:24
@SteveLovell Thanks for the head's up. Added two error traps. P. O — John Cappelletti, May 08 '17 at 21:29
Thanks + 1 , I was working on it 1 hour ago :D , Thanks @JohnCappelletti — ahmed abdelqader, May 08 '17 at 21:30

Split Address field

1 Answers1