Converting 1 Address line to two without messing up address line
-
I need help taking an address field that is 50 characters and need to split it into two fields of 25 characters. The problem is when the wording gets split between rows, the post office will not accept it. Below are some address fields that are getting split incorrectly.
1011BENT TREE LANE APT. O
6033 N. SHERIDAN RD. APT. 12-G
4065B EAST BREWINGTON ROAD
1600 PARK CIRCLE APT. 1113
Field 1 Field2
1011BENT TREE LANE APT. O
6033 N. SHERIDAN RD. APT . 12-G
4065B EAST BREWINGTON ROA D
1600 PARK CIRCLE APT. 11 13
What I need:
Field 1 Field2
1011BENT TREE LANE APT. O
6033 N. SHERIDAN RD. APT. 12-G
4065B EAST BREWINGTON ROAD
1600 PARK CIRCLE APT. 1113
I know this is too difficult to me as I am a newbie.
Any help is appreciated.
-
I'm sure there is a cleaner way to do this but it works
left side
select left('4065B EAST BREWINGTON ROAD',25- charindex(' ',reverse(left('4065B EAST BREWINGTON ROAD',25))))
right side
select right('4065B EAST BREWINGTON ROAD',len('4065B EAST BREWINGTON ROAD')-(25- charindex(' ',reverse(left('4065B EAST BREWINGTON ROAD',25)))+1))
substitute your field name for where I have the actual address
-
This should handle a few more rules. Such as the apartment rule you are using. Obviously your address line 2 could still be over 25 characters if the address to split is near 50 charatcers long and we split on a space at say the 20 character mark.
You could also make this into a couple functions to return the information.
CREATE TABLE #SplitAddress
( AddressToSplit varchar(50),
Address1 varchar(50),
Address2 varchar(50) )
INSERT INTO #SplitAddress ( AddressToSplit ) VALUES ( '4065B EAST BREWINGTON ROAD' )
INSERT INTO #SplitAddress ( AddressToSplit ) VALUES ( '1011BENT TREE LANE APT. O' )
INSERT INTO #SplitAddress ( AddressToSplit ) VALUES ( '6033 N. SHERIDAN RD. APT. 12-G' )
INSERT INTO #SplitAddress ( AddressToSplit ) VALUES ( '1600 PARK CIRCLE APT. 1113' )
INSERT INTO #SplitAddress ( AddressToSplit ) VALUES ( 'TEST APT. 123' )
--split on space near 25th place
UPDATE #SplitAddress
SET Address1 = left(AddressToSplit,25 - charindex(' ',reverse(left(AddressToSplit,25)))),
Address2 = right(AddressToSplit,len(AddressToSplit)-(25- charindex(' ',reverse(left(AddressToSplit,25)))+1))
WHERE charindex(' ',AddressToSplit) > 0
AND Len(AddressToSplit) > 25
--check for APT rule
UPDATE #SplitAddress
SET Address2 = SUBSTRING(Address1, charindex('APT',Address1), (LEN(Address1) + 1 - charindex('APT',Address1)) ) + ' ' + LTRIM(Address2),
Address1 = LTRIM(LEFT(Address1,charindex('APT',Address1)-1))
WHERE charindex('APT', Address1) > 0
--there were no spaces. split at the 25
UPDATE #SplitAddress
SET Address1 = LEFT(AddressToSplit,25),
Address2 = RIGHT(AddressToSplit, LEN(AddressToSplit) - 25)
WHERE charindex(' ',AddressToSplit) = 0 AND len(AddressToSplit) > 25
--there were no spaces. and it isnt 25
UPDATE #SplitAddress
SET Address1 = AddressToSplit
WHERE len(AddressToSplit) < 25
SELECT * FROM #SplitAddress
-
Based on the data supplied
Field1
LEFT([Address],LEN([Address])-CHARINDEX(' ',REVERSE([Address]),CHARINDEX(' .',REVERSE([Address]))+1))
Field2
SUBSTRING([Address],LEN([Address])-CHARINDEX(' ',REVERSE([Address]),CHARINDEX(' .',REVERSE([Address]))+1)+2,255)
If the data varies from the supplied standard or there are trailing spaces then the above will not work without adding RTRIM to the Address column
Far away is close at hand in the images of elsewhere.
Anon. -
The above advice is good and you should be able to use those ideas.
What you want to do is gather as many examples of your addresses that need splitting and devise a set of rules that make sense and be sure you order them correctly.
Like if you have "apt" and "apt.", be sure you search the longer one first so those get moved to two lines and if there isn't a match for apt., then apt is found next and split.
-
Thanks for the replies!
Joel's worked great!
There are few address1's longer than 25 with David's. Some are listed below:
1600 PLATT SPRINGS ROAD APT 48
1200 ST. ANDREWS ROAD APT 19-11
UNIVERSITY OAKS 21 NATIONAL GUARD RD. APT. APT 223B
P.O. BOX 5535 -OFFICIAL, OR 152 QUARRY HILL ROAD, APT. APT
1600 LONG CREEK DRIVE 1692 J
7651 GARNERS FERRY ROAD APT 1505
7651 GARNERS FERRY RD APT 306
100 WALDEN HEIGHTS DRIVE APT 714
132 SAMMY RD. APT. P. O. BOX 373
Not bad out of 900.
-
This is what I ended up using:
--split on space near 25th place
UPDATE #SplitAddress
SET Address1 = left(AddressToSplit,25 - charindex(' ',reverse(left(AddressToSplit,25)))),
Address2 = right(AddressToSplit,len(AddressToSplit)-(25- charindex(' ',reverse(left(AddressToSplit,25)))+1))
WHERE charindex(' ',AddressToSplit) > 0
AND Len(AddressToSplit) > 25
--check for APT rule
UPDATE #SplitAddress
SET Address2 = SUBSTRING(Address1, charindex('APT',Address1), (LEN(Address1) + 1 - charindex('APT',Address1)) ) + ' ' + LTRIM(Address2),
Address1 = LTRIM(LEFT(Address1,charindex('APT',Address1)-1))
WHERE charindex('APT', Address1) > 0
--strip APT for address2
UPDATE #SplitAddress
SET Address2 = SUBSTRING(AddressToSplit, charindex('APT',AddressToSplit), (LEN(AddressToSplit) + 1 - charindex('APT',AddressToSplit)) )
WHERE charindex('APT', AddressToSplit) > 0
-- used to remove apt from address1
UPDATE #SplitAddress
SET Address1 = LTRIM(LEFT(Address1,charindex('APT',Address1)-1))
WHERE charindex('APT', Address1) > 0
--there were no spaces. split at the 25
UPDATE #SplitAddress
SET Address1 = LEFT(AddressToSplit,25),
Address2 = RIGHT(AddressToSplit, LEN(AddressToSplit) - 25)
WHERE charindex(' ',AddressToSplit) = 0 AND len(AddressToSplit) > 25
--there were no spaces. and it isnt 25
UPDATE #SplitAddress
SET Address1 = Address
WHERE len(AddressToSplit) < 25
-- fix and null address1
update #SplitAddress
set address1 = Address
where address1 is null
-- select AddressToSplit, address1, address2 from #SplitAddress
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply