--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (代码 varchar(5),名称 varchar(3))
insert into #T
select 'a001','...' union all
select 'a002','...' union all
select 'a003','...' union all
select 'b001','...' union all
select 'b002','...' union all
select 'zc001','...'
go
select 代码,left(代码,patindex('%[0-9]%',代码)-1) 前缀
from #T
go
drop table #T

/*
代码    前缀
----- -----
a001  a
a002  a
a003  a
b001  b
b002  b
zc001 zc

(6 row(s) affected)

*/

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