Test it:
DECLARE @tmp TABLE ([Name] VARCHAR(30))
INSERT INTO @tmp ([Name])
VALUES('Ajil S (C1185)'),
('Ajil S (C1185)'),
('Joe P (C1157)'),
('Joe(A1160)'),
('Raj (A1174)'),
('Sreemathi (A1190)'),
('Nadarajan P (C1187)'),
('Sreemathi S (C1230)'),
('Rajakumaran C (C1192)')
SELECT RTRIM(LEFT([Name],CHARINDEX('(',[Name])-1)) AS NewName
FROM @tmp
Result:
Ajil S
Ajil S
Joe P
Joe
Raj
Sreemathi
Nadarajan P
Sreemathi S
Rajakumaran C