--改一下:select MBWZ,Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) from T_Send_Measure where Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) > '2'
len(MBWZ) - 4小于0。
--根据楼主的结果要求,这样改应该也可以:select MBWZ,Left(MBWZ,len(MBWZ) - 4) from T_Send_Measure where len(MBWZ)>4 and Left(MBWZ,len(MBWZ) - 4) > '2'
加多一个条件即可(忽略MBWZ不足4位的行): select MBWZ,Left(MBWZ,len(MBWZ) - 4) from T_Send_Measure where len(MBWZ)>=4 and Left(MBWZ,len(MBWZ) - 4) > '2'
--测试--测试数据 create table T_Send_Measure(MBWZ varchar(10)) insert T_Send_Measure select '12712' union all select '303043' union all select '1' union all select '2' go--查询方法1 select MBWZ,Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) from T_Send_Measure where Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) > '2'--查询方法2 select MBWZ,Left(MBWZ,len(MBWZ) - 4) from T_Send_Measure where len(MBWZ)>4 and Left(MBWZ,len(MBWZ) - 4) > '2' go--删除测试 drop table T_Send_Measure/*--测试结果 MBWZ ---------- ---------- 303043 30(所影响的行数为 1 行)MBWZ ---------- ---------- 303043 30(所影响的行数为 1 行) --*/
from T_Send_Measure
where Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) > '2'
from T_Send_Measure
where len(MBWZ)>4 and
Left(MBWZ,len(MBWZ) - 4) > '2'
select MBWZ,Left(MBWZ,len(MBWZ) - 4) from T_Send_Measure where len(MBWZ)>=4 and Left(MBWZ,len(MBWZ) - 4) > '2'
create table T_Send_Measure(MBWZ varchar(10))
insert T_Send_Measure
select '12712'
union all select '303043'
union all select '1'
union all select '2'
go--查询方法1
select MBWZ,Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end)
from T_Send_Measure
where Left(MBWZ,case when len(MBWZ)<4 then 0 else len(MBWZ) - 4 end) > '2'--查询方法2
select MBWZ,Left(MBWZ,len(MBWZ) - 4)
from T_Send_Measure
where len(MBWZ)>4 and
Left(MBWZ,len(MBWZ) - 4) > '2'
go--删除测试
drop table T_Send_Measure/*--测试结果
MBWZ
---------- ----------
303043 30(所影响的行数为 1 行)MBWZ
---------- ----------
303043 30(所影响的行数为 1 行)
--*/