I have a field called Address1 in my table Table1.
Here is an example of data in that field -
8 Brick Lane and 11 Balkerne Drive
I want in a query to spit the street and the number but am struggling with how to achieve this.
Any help would be greatful.
SELECT LEFT(Address1, PATINDEX('%[a-z]%', Address1)- 1) as HouseNumber, SUBSTRING(Address1, PATINDEX('%[a-z]%', Address1), LEN(Address1)) as Street FROM Table1
Using PATINDEX to find when HouseName begin, in this way you can separate address.
This the expected result:
HouseNumber Street 8 Brick Lane 11 Balkerne Drive
I hope this help.
I am asssuming you want to split the number from the text, you can use
SELECT Left(Address1,CHARINDEX(' ',Address1,0)-1) as houseNumber, Right(Address1,Len(Address1)-CHARINDEX(' ',Address1,0)) as houseStreet From Table1