declare @t table (col varchar(30))
insert into @t
select 'qadabcd (1werwe)' union all
select 'adfabcd'

select left(col,charindex('abcd',col)-1) from @t
/*
qad
adf
*/