字段 FP VARCHAR 类型,表明 FPnm
AP001
AP002
AP003
AP004
AP005
AP006
AP007
AP008
AP0051
AP0052
AP0060我要差 大于等于 AP003 小于等于 AP007,如何查查?FP >='AP003' and FP <='AP007' 会把AP0051,AP0052,AP0060
也查出来。可能有的记录是,
AP011
AP012
AP013
AP14
AP015
AP016
AP017
AP018例如AP14,少了一个0, 但是这样的话就会影响排序。 请问如何进行这个排序?
AP001
AP002
AP003
AP004
AP005
AP006
AP007
AP008
AP0051
AP0052
AP0060我要差 大于等于 AP003 小于等于 AP007,如何查查?FP >='AP003' and FP <='AP007' 会把AP0051,AP0052,AP0060
也查出来。可能有的记录是,
AP011
AP012
AP013
AP14
AP015
AP016
AP017
AP018例如AP14,少了一个0, 但是这样的话就会影响排序。 请问如何进行这个排序?
select * from tb where substring(fp,3,len(fp))>='AP003'
and substring(fp,3,len(fp))<='AP007'
order by substring(fp,3,len(fp))
and cast(substring(fp,3,len(fp)) as int)<=7
order by cast(substring(fp,3,len(fp)) as int)
go
--> -->
declare @FPnm table([FP] nvarchar(6))
Insert @FPnm
select N'AP001' union all
select N'AP002' union all
select N'AP003' union all
select N'AP004' union all
select N'AP005' union all
select N'AP006' union all
select N'AP007' union all
select N'AP008' union all
select N'AP0051' union all
select N'AP0052' union all
select N'AP0060'
Select * from @FPnm where cast(stuff([FP],1,2,'') as int) between 3 and 7
/*
FP
AP003
AP004
AP005
AP006
AP007
*/
select fp from fpnm where cast(isnull(stuff(fp,1,2,''),0) as int) between 3 and 7
select * from FPnm where convert(int,right(FP,3))>=3 and convert(int,right(FP,3))<=7