关于sql charindex查询问题
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from test where charindex('2,',DepartID)>0
--返回所有记录
--想要的结果是只返回,前两条记录
select * from test1 where charindex(',2,',DepartID)>0
select top 2 * from #test where charindex('2,',DepartID)>0
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from test where charindex(','+'2,',','+DepartID+',')>0
/*
IdDepartID
11,2,3
21,2,4
*/
--返回所有记录
--想要的结果是只返回,前两条记录
select * from test
where charindex(',2,',','+DepartID+',')>0
/*
Id DepartID
----------- --------------------------------------------------
1 1,2,3
2 1,2,4
(2 row(s) affected)
*/
create table test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
select * from test where charindex(',2,',','+DepartID+',')>0
/*
IdDepartID
11,2,3
21,2,4
*/
create table #test
(
Id int identity,
DepartID varchar(500)
)
--DepartID 插入部门id,以为,作为分隔
go
INSERT INTO #test (DepartID)
select '1,2,3' union
select '1,2,4' union
select '11,12,13' union
select '11,12,14' union
select '11,12,14' union
select '11,22,33'
--如果取部门ID包含为2的
select * from #test where charindex(',2,',DepartID)>0
--返回所有记录
--想要的结果是只返回,前两条记录
Id DepartID
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3
2 1,2,4
(2 行受影响)