I'm with Lowell on cleaning up the data.

However, this might help you, but you still have to review the matches given.

;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

JOIN Table2 ON Table1.Appname LIKE SUBSTRING( Table2.Appname, CHARINDEX( '(',Table2.Appname) + 1, CHARINDEX( ')',Table2.Appname) - CHARINDEX( '(',Table2.Appname) - 1) + '%'