MSSQL字符串截取如表名goods ,列名code
当code列内容只有后缀为s1 s2 s3 s4 s5 s5 s6 s7 s8,并且后缀只有这两个字符时才截取
81234S1
81234S2
81234S3
81234S4
81234S5
81234S6
81234S7
81234S88899S1345
8899S2345
S2123123123
2345234S5
2345234S512341231123X1要求结果如果如下:
81234
81234
81234
81234
81234
81234
81234
812348899S1345
8899S2345
S2123123123
2345234
234523412341231123X1
当code列内容只有后缀为s1 s2 s3 s4 s5 s5 s6 s7 s8,并且后缀只有这两个字符时才截取
81234S1
81234S2
81234S3
81234S4
81234S5
81234S6
81234S7
81234S88899S1345
8899S2345
S2123123123
2345234S5
2345234S512341231123X1要求结果如果如下:
81234
81234
81234
81234
81234
81234
81234
812348899S1345
8899S2345
S2123123123
2345234
234523412341231123X1
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
then left(a,len(a)-2) else a end as a
from TB
--> 测试数据: [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 行受影响)
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 a like '%S[1-9]' then left(a,len(a)-2) else a end as a
from TB
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