Or, if you cannot rely on directionals being the last piece of Address strings, you may wish to use my original join, bit with an additional check that the Suffix you found is the last one in the string.

In SQL term - there are no other words matching Suffixes after the one you've found:

SELECT [Address_pk], [Address], --SUBSTRING(a.Address, CHARINDEX( ss.PrimaryStreetSuffixName,a.Address)+LEN(ss.PrimaryStreetSuffixName), LEN(a.Address)),

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

FROM [dbo].[Address] a

INNER JOIN (SELECT DISTINCT PrimaryStreetSuffixName, [PostalServiceStandardSuffixAbbreviation] FROM [dbo].[StreetSuffix]) ss

ON a.[Address] + ' ' LIKE '% ' + ss.PrimaryStreetSuffixName + ' %'

WHERE NOT EXISTS (SELECT *

FROM [dbo].[StreetSuffix] ss1

WHERE (

SUBSTRING(a.Address, CHARINDEX( ss.PrimaryStreetSuffixName,a.Address)+LEN(ss.PrimaryStreetSuffixName), LEN(a.Address)) LIKE '% '+ss1.PrimaryStreetSuffixName+'%'

OR

SUBSTRING(a.Address, CHARINDEX( ss.PrimaryStreetSuffixName,a.Address)+LEN(ss.PrimaryStreetSuffixName), LEN(a.Address)) LIKE '% '+ss1.CommonlyUsedStreetSuffixOrAbbreviation+'%'

)

)

UNION

SELECT [Address_pk], [Address], --SUBSTRING(a.Address, CHARINDEX( ss.CommonlyUsedStreetSuffixOrAbbreviation,a.Address)+LEN(ss.CommonlyUsedStreetSuffixOrAbbreviation), LEN(a.Address)),

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

FROM [dbo].[Address] a

INNER JOIN [dbo].[StreetSuffix] ss on a.[Address] + ' ' LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation + ' %' AND not (a.[Address] + ' ' LIKE '% ' + ss.PrimaryStreetSuffixName + ' %')

WHERE NOT EXISTS (SELECT *

FROM [dbo].[StreetSuffix] ss1

WHERE (

SUBSTRING(a.Address, CHARINDEX( ss.CommonlyUsedStreetSuffixOrAbbreviation,a.Address)+LEN(ss.CommonlyUsedStreetSuffixOrAbbreviation), LEN(a.Address)) LIKE '% '+ss1.PrimaryStreetSuffixName+'%'

OR

SUBSTRING(a.Address, CHARINDEX( ss.CommonlyUsedStreetSuffixOrAbbreviation,a.Address)+LEN(ss.CommonlyUsedStreetSuffixOrAbbreviation), LEN(a.Address)) LIKE '% '+ss1.CommonlyUsedStreetSuffixOrAbbreviation+'%'

)

)

UNION

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

FROM [dbo].[Address] a

WHERE NOT EXISTS (

SELECT *

FROM [dbo].[StreetSuffix] ss WHERE (

a.[Address] + ' ' LIKE '% ' + ss.CommonlyUsedStreetSuffixOrAbbreviation + ' %'

OR a.[Address]+ ' ' LIKE '% ' + ss.PrimaryStreetSuffixName + ' %')

)

ORDER BY [Address_pk]

Try to compare performance of it with "APPLY" version on your machine.

On my laptop, on the small dataset, the old good INNER JOIN is on average twice as fast comparing to APPLY version.