In this case you can dynamically remove them from Address strings and then match only the last word of remaining strings to the Street Suffixes:

CREATE TABLE dbo.AddressDirectional (

ID TINYINT IDENTITY (1,1) PRIMARY KEY NOT NULL,

Code VARCHAR(5) COLLATE DATABASE_DEFAULT NOT NULL,

UNIQUE (Code)

)

INSERT INTO dbo.AddressDirectional (Code)

SELECT 'N'

UNION

SELECT 'S'

UNION

SELECT 'E'

UNION

SELECT 'W'

UNION

SELECT 'NE'

UNION

SELECT 'NW'

UNION

SELECT 'SE'

UNION

SELECT 'SW'

SELECT [Address_pk], [Address],

REPLACE([Address],ss.[PrimaryStreetSuffixName],ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]

FROM [dbo].[Address] a

LEFT JOIN dbo.AddressDirectional d ON a.Address LIKE '% ' + d.Code

INNER JOIN [dbo].[StreetSuffix] ss on RTRIM(ISNULL(LEFT(a.Address, LEN(a.Address)-LEN(' ' + d.Code)), a.[Address])) LIKE '% ' + ss.PrimaryStreetSuffixName

UNION

SELECT [Address_pk], [Address],

REPLACE([Address],ss.[CommonlyUsedStreetSuffixOrAbbreviation], ss.[PostalServiceStandardSuffixAbbreviation]) AS [AddressAbbreviated]

FROM [dbo].[Address] a

LEFT JOIN dbo.AddressDirectional d ON a.Address LIKE '% ' + d.Code

INNER JOIN [dbo].[StreetSuffix] ss on RTRIM(ISNULL(LEFT(a.Address, LEN(a.Address)-LEN(' ' + d.Code)), a.[Address])) LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation and not (a.[Address] LIKE '% ' + ss.PrimaryStreetSuffixName )

UNION

SELECT [Address_pk], [Address], 'N/A'

FROM [dbo].[Address] a

LEFT JOIN dbo.AddressDirectional d ON a.Address LIKE '% ' + d.Code

WHERE NOT EXISTS (

SELECT *

FROM [dbo].[StreetSuffix] ss WHERE a.[Address] LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation

OR (RTRIM(ISNULL(LEFT(a.Address, LEN(a.Address)-LEN(' ' + d.Code)), a.[Address])) LIKE '% ' + ss.PrimaryStreetSuffixName )

)

ORDER BY [Address_pk]