you are going to have to cleanup the data, it looks to me;

in your example, using charindex, you only get a single match.

unless you can guarantee the three letter abbreviations won't return false matches ("COR" would find "Google Corporation", for example), i don't see anything to reliably join the data.

/*

AppIDAppnameAppIDAppname

NullCSP2Credit Servicing Portal(CSP)

*/

;With Table1([AppID],[Appname])

AS

(

SELECT 'Null','CSO Services Operations' UNION ALL

SELECT 'Null','CSP' UNION ALL

SELECT 'Null','AAC Claims Systems'

),Table2([AppID],[Appname])

AS

(

SELECT '1','Corporate Services Operations(CSO)' UNION ALL

SELECT '2','Credit Servicing Portal(CSP)' UNION ALL

SELECT '3','American Assuarance Company (AAC) Claims Systems'

)

SELECT *

FROM Table1

INNER JOIN Table2

ON CHARINDEX(Table1.Appname,Table2.Appname) > 0