create table a(id int) insert into a select 1 insert into a select 2 insert into a select 3create table b (idzh varchar(50)) insert into b select '1' insert into b select '2,3' insert into b select '4,5'select * from a , b where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0id idzh 1 1 2 2,3 3 2,3
select * from sc where charindex(','+left(id,10)+',',','+number+',')>0
create table b (idzh varchar(50)) insert into b select '1' insert into b select '2,3' insert into b select '4,5'declare @i int set @i=2 select * from b where charindex(','+ltrim(@i)+',',','+idzh+',')>02,3
declare @i int set @i=1 select * from sc where charindex(','+left(id,10)+',',','+number+',')>0 and id=@id
select * from a , b where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0','+......+',' 第一个数字前并没有","呀!---这仅是为了定位的需要,不影响你的查询和表的记录
create table sc (Id varchar(50),Number varchar(50)) insert into sc select '1','1,2,3,5,21,' insert into sc select '2','2,8,4,112,' insert into sc select '3','1,5,9,15,'select * from sc where charindex('2',Number)>0 --drop table sc
create PROCEDURE A @i varchar as create table #sc (Id varchar(50),Number varchar(50)) insert into #sc select '1','1,2,3,5,21,' insert into #sc select '2','2,8,4,112,' insert into #sc select '3','1,5,9,15,'select * from #sc where charindex(@i,Number)>0select @i GO exec A '2' drop PROCEDURE A
最后加一句 drop table #sc
Number字段内容最前面加个“,”号 就可以用Like匹配了
1 1,2,3,5,21 2 2,8,4,112 3 1,5,9,15 declare @i int set @i=1 select * from sc where charindex(','+left(id,10)+',',','+number+',')>0 and id=@id ,1, ,1,2,3,5,21, ,2, ,2,8,4,112, ,3, ,1,5,9,15, ,1,,1,2,3,5,21, ,2, ,2,8,4,112, ,3, ,1,5,9,15, ,1, ,1,2,3,5,21, ,2,,2,8,4,112, ,3, ,1,5,9,15,,1, ,1,2,3,5,21, ,2, ,2,8,4,112, ,3, ,1,5,9,15,
不加逗号select * from sc where charindex(left(id,10),number)> 0 and id=@id 11,2,3,5,21 2 2,8,4,112 3 1,5,9,15 1 1,2,3,5,21 22,8,4,112 3 1,5,9,15 1 1,2,3,5,21 2 2,8,4,112 3 1,5,9,15
insert into a select 1
insert into a select 2
insert into a select 3create table b (idzh varchar(50))
insert into b select '1'
insert into b select '2,3'
insert into b select '4,5'select * from a , b
where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0id idzh
1 1
2 2,3
3 2,3
insert into b select '1'
insert into b select '2,3'
insert into b select '4,5'declare @i int
set @i=2
select * from b where charindex(','+ltrim(@i)+',',','+idzh+',')>02,3
set @i=1
select * from sc where charindex(','+left(id,10)+',',','+number+',')>0 and id=@id
where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0','+......+','
第一个数字前并没有","呀!---这仅是为了定位的需要,不影响你的查询和表的记录
create table sc (Id varchar(50),Number varchar(50))
insert into sc select '1','1,2,3,5,21,'
insert into sc select '2','2,8,4,112,'
insert into sc select '3','1,5,9,15,'select * from sc
where charindex('2',Number)>0
--drop table sc
create PROCEDURE A @i varchar
as
create table #sc (Id varchar(50),Number varchar(50))
insert into #sc select '1','1,2,3,5,21,'
insert into #sc select '2','2,8,4,112,'
insert into #sc select '3','1,5,9,15,'select * from #sc
where charindex(@i,Number)>0select @i
GO
exec A '2'
drop PROCEDURE A
drop table #sc
就可以用Like匹配了
2 2,8,4,112
3 1,5,9,15
declare @i int
set @i=1
select * from sc where charindex(','+left(id,10)+',',','+number+',')>0 and id=@id
,1, ,1,2,3,5,21,
,2, ,2,8,4,112,
,3, ,1,5,9,15,
,1, ,1,2,3,5,21,
,2, ,2,8,4,112,
,3, ,1,5,9,15,
,1, ,1,2,3,5,21,
,2, ,2,8,4,112,
,3, ,1,5,9,15,,1, ,1,2,3,5,21,
,2, ,2,8,4,112,
,3, ,1,5,9,15,
1 1,2,3,5,21
2 2,8,4,112
3 1,5,9,15 1 1,2,3,5,21
2 2,8,4,112
3 1,5,9,15
1 1,2,3,5,21
2 2,8,4,112
3 1,5,9,15
CHARINDEX ( expression1 ,expression2 [ , start_location ] ) 参数
expression1
---一个表达式,其中包含要查找的字符的序列。expression1 是一个字符串数据类别的表达式。expression2
---一个表达式,通常是一个为指定序列搜索的列。expression2 属于字符串数据类别。start_location (此参数可省略,省略后为默认,从expression2中所代表或返回字符串的头算起)
---开始在 expression2 中搜索 expression1 时的字符位置。如果 start_location 未被指定、是一个负数或零,则将从 expression2 的开头开始搜索。start_location 可以是 bigint 类型。-- 返回类型
如果 expression2 的数据类型为 varchar(max)、nvarchar(max) 或 varbinary(max),则为 bigint,否则为 int。
这个是什么意思:
2:return 1,2
1:return 1,3
3:?
select *
from table_1
where index(number,1) > 0