关于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
--返回所有记录
--想要的结果是只返回，前两条记录

[解决办法]

引用:

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

Id DepartID
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1,2,3
2 1,2,4

(2 行受影响)

[解决办法]

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 行受影响)

[解决办法]