MSSQL字符串截取如表名goods ,列名code
当code列内容只有后缀为s1 s2 s3 s4 s5 s5 s6 s7 s8,并且后缀只有这两个字符时才截取
81234S1
81234S2
81234S3
81234S4
81234S5
81234S6
81234S7
81234S8
8899S1345
8899S2345
S2123123123
2345234S5
2345234S5
12341231123X1
要求结果如果如下:
81234
81234
81234
81234
81234
81234
81234
81234
8899S1345
8899S2345
S2123123123
2345234
2345234
12341231123X1
------解决方案--------------------
- SQL code
if object_id('[TB]') is not null drop table [TB]create table [TB](a varchar(13))insert [TB]select '81234S2' union allselect '81234S3' union allselect '81234S4' union allselect '81234S5' union allselect '81234S6' union allselect '81234S7' union allselect '81234S8' union allselect '8899S1345' union allselect '8899S2345' union allselect 'S2123123123' union allselect '2345234S5' union allselect '2345234S5' union allselect '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 afrom TB/*812348123481234812348123481234812348899S13458899S2345S21231231232345234234523412341231123X1
------解决方案--------------------
- 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 allselect '81234S2' union allselect '81234S3' union allselect '81234S4' union allselect '81234S5' union allselect '81234S6' union allselect '81234S7' union allselect '81234S8' union allselect '8899S1345' union allselect '8899S2345' union allselect 'S2123123123' union allselect '2345234S5' union allselect '2345234S5' union allselect '12341231123X1'--开始查询select case when col like '%S[1-8]' then LEFT(col,LEN(col)-2) else col end from [tb]--结束查询drop table [tb]/*-------------81234812348123481234812348123481234812348899S13458899S2345S21231231232345234234523412341231123X1(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 afrom 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