62,062
社区成员
发帖
与我相关
我的任务
分享
set nocount on
declare @table table (colname varchar(12))
insert into @table
select '12/31/75' union all
select '34/67/94/113' union all
select '1/56/3/16' union all
select '34/23/12/24' union all
select '90/34/45/47'
declare @i int;set @i=70--设置参数
select a.* from @table a left join master..spt_values b
on charindex('/'+ltrim(b.number)+'/','/'+a.colname+'/')>0
where b.[type]='P' and number between @i-10 and @i+10
and b.number is not null
/*
colname
------------
12/31/75
34/67/94/113
*/
set nocount on
declare @table table (colname varchar(12))
insert into @table
select '12/31/75' union all
select '34/67/94/113' union all
select '1/56/3/16' union all
select '34/23/12/24' union all
select '90/34/45/47'
declare @i int;set @i=70
declare @j int;set @j=@i-10
declare @t table(id int)
while @j<=@i+10
begin
insert into @t select @j
set @j=@j+1
end
select * from @t
select a.* from @table a left join @t b
on charindex('/'+ltrim(b.id)+'/','/'+a.colname+'/')>0
where b.id is not null
/*
colname
------------
12/31/75
34/67/94/113
*/
set nocount on
declare @table table (colname varchar(12))
insert into @table
select '12/31/75' union all
select '34/67/94/113' union all
select '1/56/3/16' union all
select '34/23/12/24' union all
select '90/34/45/47'
declare @i int;set @i=70
declare @j int;set @j=@i-10
declare @t table(id int)
while @j<@i+10
begin
insert into @t select @j
set @j=@j+1
end
select a.* from @table a left join @t b
on charindex('/'+ltrim(b.id)+'/','/'+a.colname+'/')>0
where b.id is not null
/*
colname
------------
12/31/75
34/67/94/113
*/
create table #tb(val varchar(200))
insert into #tb
select '12/25/35' union all
select '40/60/80'
go
create table #tb2(val int)
insert into #tb2
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16 union all
select 17 union all
select 18 union all
select 19 union all
select 20 union all
select 21 union all
select 22 union all
select 23 union all
select 24 union all
select 25 union all
select 26 union all
select 27 union all
select 28 union all
select 29 union all
select 30
go
select * from #tb a where exists(select 1 from #tb2 where a.val like '%'+cast(val as varchar)+'%')
/*
12/25/35
*/
--借助临时表,根据输入的数生成临时表