CODEWITHSUNDEEP
sqlsql-serverstringt-sqlsplit
ViKiNG
ViKiNG

Reputation: 1334

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

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

Sample Data

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.

Upvotes: 2

Views: 156

Answers (3)

Jason A. Long
Jason A. Long

Reputation: 4442

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

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 4

Related Questions