问题描述:

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.

Thanks

网友答案:
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
相关阅读:
Top