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
create table tb(部门编码 varchar(20), 级别 int) insert into tb values('241400000' , 0) insert into tb values('241635100' , 0) insert into tb values('246320000' , 0) goupdate 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 endselect * from tbdrop table tb/* 部门编码 级别 -------------------- ----------- 241400000 3 241635100 5 246320000 4(所影响的行数为 3 行) */
Update Tb1 Set 级别 = Case Len(Convert(int,Reverse(Right(Rtrim(部门编号),5)))) when 1 Then Case when Convert(int,Reverse(Right(Rtrim(deptid),5))) = 0 Then 3 Else 4 End When 2 Then 4 when 3 Then 5 when 4 Then 5 when 5 Then 5 End
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
insert into tb values('241400000' , 0)
insert into tb values('241635100' , 0)
insert into tb values('246320000' , 0)
goupdate 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
endselect * from tbdrop table tb/*
部门编码 级别
-------------------- -----------
241400000 3
241635100 5
246320000 4(所影响的行数为 3 行)
*/
when 1 Then Case when Convert(int,Reverse(Right(Rtrim(deptid),5))) = 0 Then 3 Else 4 End
When 2 Then 4
when 3 Then 5
when 4 Then 5
when 5 Then 5 End