--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([编码] BIGINT,[名称] VARCHAR(13))
INSERT [tb]
SELECT 3011003001,'Q40305' UNION ALL
SELECT 3011003011,'Q40305彩锌' UNION ALL
SELECT 3011003003,'Q403018' UNION ALL
SELECT 3011003012,'Q403018红锌'
GO

--> 测试语句：
DELETE t FROM [tb] AS t
WHERE EXISTS
    (
    SELECT 1 FROM [tb] WHERE [名称] LIKE t.[名称]+'%' AND t.[名称] NOT LIKE [名称]+'%'
    )

SELECT * FROM [tb]
/*
编码                   名称
-------------------- -------------
3011003011           Q40305彩锌
3011003012           Q403018红锌

(2 行受影响)


*/

------解决方案--------------------

SQL code

create table cl
(编码名称 varchar(200))

insert into cl(编码名称)
select '3011003001 （Q40305）' union all   
select '3011003011 （Q40305彩锌）' union all  
select '3011003003 （Q403018）' union all  
select '3011003012 （Q403018红锌）'


delete x from
(select row_number() over(
partition by substring(编码名称,charindex('（',编码名称)+1,
             patindex('%[^0-9]%',substring(编码名称,charindex('（',编码名称)+2,200)))
order by len(编码名称) desc) rn,编码名称      
from cl) x 
where x.rn>1

select * from cl

/*
编码名称
---------------------------
3011003011 （Q40305彩锌）
3011003012 （Q403018红锌）

(2 row(s) affected)
*/