if object_id('[TB]') is not null drop table [TB]
create table [TB](a varchar(13))
insert [TB]
select '81234S2' union all
select '81234S3' union all
select '81234S4' union all
select '81234S5' union all
select '81234S6' union all
select '81234S7' union all
select '81234S8' union all
select '8899S1345' union all
select '8899S2345' union all
select 'S2123123123' union all
select '2345234S5' union all
select '2345234S5' union all
select '12341231123X1'

select * from [TB]


select case when left(right(a,2),1) = 'S' and isnumeric(right(a,1))=1 then left(a,len(a)-2) else a end as a
from TB

/*
81234
81234
81234
81234
81234
81234
81234
8899S1345
8899S2345
S2123123123
2345234
2345234
12341231123X1

------解决方案--------------------
SQL code

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (col varchar(13))
insert into [tb]
select '81234S1' union all
select '81234S2' union all
select '81234S3' union all
select '81234S4' union all
select '81234S5' union all
select '81234S6' union all
select '81234S7' union all
select '81234S8' union all
select '8899S1345' union all
select '8899S2345' union all
select 'S2123123123' union all
select '2345234S5' union all
select '2345234S5' union all
select '12341231123X1'

--开始查询
select case when col like '%S[1-8]' then LEFT(col,LEN(col)-2) else col end from [tb]

--结束查询
drop table [tb]

/*
-------------
81234
81234
81234
81234
81234
81234
81234
81234
8899S1345
8899S2345
S2123123123
2345234
2345234
12341231123X1

(14 行受影响)

------解决方案--------------------
SQL code
select
 case when
           left(right(a,2),1) = 'S' and isnumeric(right(a,1))=1 
      then 
           left(a,len(a)-2) 
      else 
           a end as a
from tb

------解决方案--------------------
SQL code
select (case when right(code,2) between 's1' and 's8' then substring(code , 1 , len(code) - 2) else code end) code from goods