Substitute expressions in CHARINDEX and LEN with values in another table

Question

I have thousands of records that are business names and addresses strung together in a single field (NameAddress):

Microsoft 1 157th Ave NE Redmond WA 98052
Apple Inc 1 Infinite Loop Cupertino CA 95014
Kraft Beton GmbH Industriestraße 123 1220 Wien
Pletzenauer Holzbau GmbH Moosnerweg 9 6382 Kirchdorf in Tirol

I would like to parse out the cities into their own fields. I have a query that does this well but will only work for one city at a time:

CASE WHEN CHARINDEX('wien',NameAddress) > 0 THEN
SUBSTRING(NameAddress,(CHARINDEX('wien',NameAddress)),LEN('wien')) ELSE '' END
AS City

I have a table dbo.geography that contains the names of all of these cities. Is it possible to use the values in that table as the expression in the CHARINDEX and LEN commands in my above query?

In essence I am running a check to see if the string contains any values in another table and if so, create a substring containing that value.

Answer 1

This query will give you a list of NameAddress and City from geography table if it exists in NameAddress

select a.NameAddress, ISNULL(g.City, '')
from address a
left join geography g
  on CHARINDEX(g.City, a.NameAddress) > 0

SQL Fiddle demo

BTW, will it work well for real addresses? What if the name of a city is in the street name?