sql - Return records based on the result of a function -

i have records follows:

1) bel
1) mersen
a) vishay-sprague
circuit partners
bentek
circuit test

i want return distinct set if record has closing bracket, remove entire bracket prefix ( 1) mersen becomes mersen) otherwise return record is. ad hoc, 1 off query. i've tried this.

if (charindex(')', (select [manufacturer] [dbo].[qpl_itmsupac_nf]), 1) > 0)      select distinct substring([dbo].[qpl_itmsupac_nf].[manufacturer], 4, 99)     [dbo].[qpl_itmsupac_nf]  else      select distinct [dbo].[qpl_itmsupac_nf].[manufacturer]     [dbo].[qpl_itmsupac_nf] 

...but error:

subquery returned more 1 value...

the above in procedure.

thoughts?

use exists , move function subquery. in case, charindex() equivalent like ')%':

if (exists (select 1 [dbo].[qpl_itmsupac_nf] manufacturer ')%') )     select distinct substring([dbo].[qpl_itmsupac_nf].[manufacturer], 4, 99)     [dbo].[qpl_itmsupac_nf] else     select distinct [dbo].[qpl_itmsupac_nf].[manufacturer]     [dbo].[qpl_itmsupac_nf]