The following samples will find street name if address in the form of (number)(space)(street)(space)(streettype):

SELECT SUBSTRING(Address1, CHARINDEX(' ', Address1+' ', 1) +1, CHARINDEX(' ', Address1+' ', CHARINDEX(' ', Address1+' ', 1) +1) - CHARINDEX(' ', Address1+' ', 1) -1)
FROM NameAddress
WHERE ISNUMERIC (LEFT(Address1,1))=1
AND LEN(Address1) > 5