22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb(部门编码 varchar(20), 级别 int)
insert into tb values('241400000' , 0)
insert into tb values('241635100' , 0)
insert into tb values('246320000' , 0)
go
update tb set 级别 =
case when left(reverse(部门编码),5) = '00000' then 3
when left(reverse(部门编码),4) = '0000' and substring(reverse(部门编码),5,1) <> '0' then 4
when (left(reverse(部门编码),3) = '000' or left(reverse(部门编码),2) = '00' or left(reverse(部门编码),1) = '0') and substring(reverse(部门编码),4,1) <> '0' then 5
end
select * from tb
drop table tb
/*
部门编码 级别
-------------------- -----------
241400000 3
241635100 5
246320000 4
(所影响的行数为 3 行)
*/
update tb set 级别 =
case when left(reverse(部门编码),5) = '00000' then 3
when left(reverse(部门编码),4) = '0000' and substring(reverse(部门编码),5,1) <> '0' then 4
when (left(reverse(部门编码),3) = '000' or left(reverse(部门编码),2) = '00' or left(reverse(部门编码),1) = '0') and substring(reverse(部门编码),4,1) <> '0' then 5
end