> Vendor      Is_Active
 ABC(1263)        1
 efgh (187        1
 pqrs 890ag)      1
 xyz              1
 lmno(488)        1
 (9867-12)        1

ID        Name
 1263      ABC
 187      efgh
 890ag    pqrs
          xyz
 488      lmno
 9867-12

select vendor,case when vendor like '%(%' or vendor like '%)%' then Replace(Replace(right(A.Vendor,  charindex(' ', reverse(vendor)) - 1),'(',''),')','') end as 'test' from tableA a