The following Microsoft SQL Server T-SQL query will find street name if address in the form of (number)(space)(street)(space)(streettype):

-- SQL Sever list all object in database

SELECT DISTINCT StreetName=

       SUBSTRING(AddressLine1, CHARINDEX(' ', AddressLine1+' ', 1) +1,

       CHARINDEX(' ', AddressLine1+' ', CHARINDEX(' ', AddressLine1+' ', 1) +1) -

       CHARINDEX(' ', AddressLine1+' ', 1) -1)

FROM AdventureWorks2008.Person.Address

WHERE ISNUMERIC (LEFT(AddressLine1,1))=1

AND LEN(AddressLine1) > 5

/*

StreetName

....

Buchanan

Buckingham

Buckthorn

Buena

Bundros

Bureau

Burning

Burwood

Bush

Buskirk

Buskrik

....

*/

Related article:

What's the best way to parse an Address field using t-sql or SSIS?