字符串数值搜索问题

[wokaoniya]

有一张表，里面有个字段是Nvarchar类型，储存的数值例如：第一列：12/31/75 第二列：34/67/94/113。搜索的时侯输入一个数值15。那么搜索的范围就是5-25，第一列的12符合这个范围，第一列就搜出来，例如输入70.第一列的75，和第二列的67都符合搜索范围。都搜出来，这要怎么做。各位高手帮帮忙。

[laowang134]

1.是第一行还是第一列？
2.范围是加减10？

[laowang134]

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
*/
--借助临时表，根据输入的数生成临时表

[xyytuo]

[wokaoniya]

[Quote=引用 1 楼 laowang134 的回复:]
1.是第一行还是第一列？
2.范围是加减10？
[/Quote]

不要意思，是行，打错了打错了

[叶子]

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
*/

[叶子]

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--设置参数

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
*/

[weike021996]

真猛，高手

[Ayanamikula]

mark