Extract Data

I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd

I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd

I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1

But i get the following result which is no good!

Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba


What am I doing wrong in the Street & StreetSuffix Functions above?


I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.


In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))

SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))

SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))