How to split string to takeout a specific part in SQL?

Question

I have a situation where I want to split address into Street Number and Street Name.

Sample Data

1093 Hundred Line Road
Flat 4442 Holly StreetAvondale
Apartment 1401/142 Shakespeare Road
Unit K109 Northbridge 45 Akoranga Drive, Northcote

In these examples, the bold part is Street Number and rest is Street Name.

My current effort is not helping me so far. Any one got a quick solution to that?

-Key is the Street Number part of the address ends where the last occurrence of Number ends.

Does your data always consist of Streen Number? Does it always end in a number? — Felix Pamittan, Aug 04 '17 at 00:16
What are you going to do for 1200 5th St (resulting in `th St`) or 400 Main St PO Box 123 Springfield PA (`Springfield PA`)? — smdrager, Aug 04 '17 at 00:27
For 5th.... the whole word should work. Not doing Post Box Addresses. — ViKiNG, Aug 04 '17 at 01:54

score 4 · Answer 1 · answered Aug 04 '17 at 00:19

4

If the pattern of strings is consistent, you can use

select 
ltrim(rtrim(reverse(substring(reverse(address),patindex('%[0-9]%',reverse(address)),len(address))))) as streetNum,
ltrim(rtrim(reverse(substring(reverse(address),1,patindex('%[0-9]%',reverse(address))-1)))) as streetName
from tbl

Find the occurrence of first number in the reversed string using patindex and use substring and reverse to split them into separate fields.

answered Aug 04 '17 at 00:19

Vamsi Prabhala

48,685
4
36
58

Thanks, vkp. Upvoted for the effort. Verifying the results atm. :) – ViKiNG Aug 04 '17 at 02:02
My examples were not rich enough with the new address patterns that I just figured out but I'm okay to work from here onward. Both @vkp and John's answers are *almost* correct for given samples but marking John's reply as the marked answer for more details. Hope vkp is okay with that, please? – ViKiNG Aug 04 '17 at 02:38

John Cappelletti · Accepted Answer · 2017-08-04T00:57:04.243

3

Example

Declare @YourTable Table ([Id] varchar(50),[Addr] varchar(50))
Insert Into @YourTable Values 
 (1,'1093 Hundred Line Road')
,(2,'Flat 4442 Holly StreetAvondale')
,(3,'Apartment 1401/142 Shakespeare Road')
,(4,'Unit K109 Northbridge 45 Akoranga Drive, Northcote')

Select ID 
      ,Addr1 = left(Addr,len(Addr)-patindex('%[0-9]%',reverse(Addr))+1)
      ,Addr2 = ltrim(right(Addr,patindex('%[0-9]%',reverse(Addr))-1))
 From @YourTable

Or Just for Fun - Using a Cross Apply

Select ID 
      ,Addr1 = substring(Addr,1,B.Pos)
      ,Addr2 = ltrim(substring(Addr,B.Pos+1,100))
 From @YourTable
 Cross Apply (values (len(Addr)-patindex('%[0-9]%',reverse(Addr))+1)) B(Pos)

Returns

ID  Addr1                       Addr2
1   1093                        Hundred Line Road
2   Flat 4442                   Holly StreetAvondale
3   Apartment 1401/142          Shakespeare Road
4   Unit K109 Northbridge 45    Akoranga Drive, Northcote

I should note:

Parsing an address can be a slippery slope. Consider the following: Address standardization within a database

edited Aug 04 '17 at 00:57

answered Aug 04 '17 at 00:21

John Cappelletti

79,615
7
44
66

Thanks, John. Agreed with standardization but data coming from 7 different systems with 30 yr+ old records and 70k plus clients. Everything is split in Address1Line1, Address1Line2, Address1Line3, Address1Line4, PostCode1, City1, Suburb1, Country1 and for N number of addresses..yet data entry issues cause stupid problems. – ViKiNG Aug 04 '17 at 01:57
Thanks, John. Upvoted for effort. Verifying the results atm. :) – ViKiNG Aug 04 '17 at 02:02
1

@Viking Happy to help – John Cappelletti Aug 04 '17 at 02:04
My examples were not rich enough with the new address patterns that I just figured out but I'm okay to work from here onward. Both @vkp and John's answers are *almost* correct for given samples but marking John's reply as the marked answer for more details. Hope vkp is okay with that, please? – ViKiNG Aug 04 '17 at 02:38

Jason A. Long · Answer 3 · 2017-08-04T01:13:41.527

Another option...

IF OBJECT_ID('tempdb..#Address', 'U') IS NOT NULL 
DROP TABLE #Address;

CREATE TABLE #Address (
    StreetAddress VARCHAR(50) NOT NULL 
    );
INSERT #Address (StreetAddress) VALUES
    ('1093 Hundred Line Road'),
    ('Flat 4442 Holly StreetAvondale'),
    ('Apartment 1401/142 Shakespeare Road'),
    ('Unit K109 Northbridge 45 Akoranga Drive, Northcote');

--  SELECT * FROM #Address a;

--========================================================

SELECT 
    *,
    StreetNum = LEFT(a.StreetAddress, sl.SplitLocation),
    StreetName = SUBSTRING(a.StreetAddress, sl.SplitLocation + 1, 50)
FROM
    #Address a
    CROSS APPLY ( VALUES (PATINDEX('%[^0-9] [0-9]%', REVERSE(a.StreetAddress))) ) rs (ReverseSplit)
    CROSS APPLY ( VALUES (LEN(a.StreetAddress) - rs.ReverseSplit) ) sl (SplitLocation);

HTH, Jason

Thanks, Jason. Upvoted for effort. Verifying the results though :) — ViKiNG, Aug 04 '17 at 02:02

How to split string to takeout a specific part in SQL?

3 Answers3