Here is an addition to the previous code, nothing fancy but it should work most of the time

😎

USE tempdb;

GO

SET NOCOUNT ON;

SELECT

AW.Id

,CS.city_state_ID

,AW.Address

,CASE

WHEN CHARINDEX(CS.City,AW.Address,1) = 0 THEN AW.Address

WHEN CHARINDEX(CS.City,AW.Address,1) > 0 THEN

CASE

WHEN RIGHT(SUBSTRING(AW.Address,1,CHARINDEX(CS.City,AW.Address,1) - 2),1) IN (' ',',') THEN SUBSTRING(AW.Address,1,CHARINDEX(CS.City,AW.Address,1) - 3)

ELSE SUBSTRING(AW.Address,1,CHARINDEX(CS.City,AW.Address,1) - 2)

END

ELSE ''

END

,CS.CITY

,CS.State

FROM dbo.TBL_ADDRESS_WORKTABLE AW

CROSS APPLY dbo.tbl_city_state CS

WHERE

(

AW.city = CS.City

AND

AW.State = CS.State

)

OR

(

CHARINDEX(CS.CITY, AW.Address,1) > 0

AND

CHARINDEX(CS.State, AW.Address,1) > 0

)

OR

(

CHARINDEX(REPLACE(CS.CITY,CHAR(32),''), AW.Address,1) > 0

AND

CHARINDEX(CS.State, AW.Address,1) > 0

)

;

Quick thought, mis-spellings could be handled via alternative name/spelling table.