34,575
社区成员
发帖
与我相关
我的任务
分享
declare @s as nvarchar(20)
declare @e as nvarchar(20)
set @s='111-3'
set @e='445-3000'
select * from #tb where cast(left(col,charindex('-',col)-1) as int) between
cast(left(@s,charindex('-',@s)-1) as int) and
cast(left(@e,charindex('-',@e)-1) as int)
and ((left(col,charindex('-',col)-1)<>left(@s,charindex('-',@s)-1))
or
(left(col,charindex('-',col)-1)=left(@s,charindex('-',@s)-1)
and cast(stuff(col,1,charindex('-',col),'') as int)>=cast(stuff(@s,1,charindex('-',@s),'') as int)))
and ((left(col,charindex('-',col)-1)<>left(@e,charindex('-',@e)-1))
or
(left(col,charindex('-',col)-1)=left(@e,charindex('-',@e)-1)
and cast(stuff(col,1,charindex('-',col),'') as int)<=cast(stuff(@e,1,charindex('-',@e),'') as int)))
order by left(col,charindex('-',col)-1),len(stuff(col,1,charindex('-',col),''))
create table #tb(col nvarchar(50))
insert #tb
select '111-1' union all
select '222-3' union all
select '111-88' union all
select '222-66' union all
select '445-1' union all
select '445-2' union all
select '445-3' union all
select '445-4' union all
select '445-5' union all
select '445-6' union all
select '445-3000' union all
select '445-20' union all
select '446-30' union all
select '446-2' union all
select '445-23'
declare @s as nvarchar(20)
declare @e as nvarchar(20)
set @s='111-3'
set @e='446-2'
select * from #tb where cast(left(col,charindex('-',col)-1) as int) between
cast(left(@s,charindex('-',@s)-1) as int) and
cast(left(@e,charindex('-',@e)-1) as int)
and ((left(col,charindex('-',col)-1)<>left(@s,charindex('-',@s)-1))
or
(left(col,charindex('-',col)-1)=left(@s,charindex('-',@s)-1) and cast(stuff(col,1,charindex('-',col),'') as int)>=cast(stuff(@s,1,charindex('-',@s),'') as int)))
and ((left(col,charindex('-',col)-1)<>left(@e,charindex('-',@s)-1))
or
(left(col,charindex('-',col)-1)=left(@e,charindex('-',@s)-1) and cast(stuff(col,1,charindex('-',col),'') as int)<=cast(stuff(@e,1,charindex('-',@e),'') as int)))
order by col
create table #tb(col nvarchar(50))
insert #tb
select '445-1' union all
select '445-2' union all
select '445-3' union all
select '445-4' union all
select '445-5' union all
select '445-6' union all
select '445-7' union all
select '445-20' union all
select '500-1' union all
select '500-2' union all
select '445-23'
select * from #tb where left(col,3)='445'
and cast(stuff(col,1,4,'') as int) between 1 and 20
select * from createTable where xh like ('445-[2-9]') or xh like ('445-1[0-9]')
select * from createTable where xh like ('445-[2-9]' or '445-1[0-9]')
with cte as
(
select row_number() over(order by getdate()) no,* from createTable
)
select * from cte where no>=(select min(no) from cte where xh='445-2')
and no<=(select max(no) from cte where xh='445-20')
where
left(xh,4)='445-' and (cast(right(xh,len(xh)-4) as int) between 1 and 20)
---xh>='445-2' and xh<'445-20'