create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13'
go
create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'
go
;with ach as
(
select a.rid,a.role_id,substring(a.role_id,b.number,charindex(',',a.role_id+',',b.number) - b.number) role_id1
from (select rid=row_number() over (order by getdate()),role_id from A) a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.role_id)
and substring(','+a.role_id,b.number,1) = ','
)
select distinct b.*
from B b,ach t
where charindex(','+t.role_id1+',',','+b.role_popedom+',') > 0 and t.role_id = '2,3'
drop table A,B
/*************************
role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15
2,3,5,12,16,17,18
(2 行受影响)
create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13'
create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'
select b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',role_popedom)>0
and role_id='2,3'
drop table a,b
/*
role_popedom
----------------------
1,3,5,11,13,15
2,3,5,12,16,17,18
（所影响的行数为 2 行）
*/
declare @str varchar(10)
set @str='2,3'
select * from 表B where patindex('%['+@str+']%',role_popedom)>0
create table A(role_id varchar(10))
insert into A
select '1' union all
select '1,3' union all
select '2,3' union all
select '5,13' union all
select '3' --添加3的信息
go
create table B(role_popedom varchar(100))
insert into B
select '1,3,5,11,13,15' union all
select '2,3,5,12,16,17,18' union all
select '5,13'
go
;with ach as
(
select a.rid,a.role_id,substring(a.role_id,b.number,charindex(',',a.role_id+',',b.number) - b.number) role_id1
from (select rid=row_number() over (order by getdate()),role_id from A) a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.role_id)
and substring(','+a.role_id,b.number,1) = ','
)
select distinct b.*
from B b,ach t
where charindex(','+t.role_id1+',',','+b.role_popedom+',') > 0 and t.role_id = '3'
/*
为1的时候
role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15
(1 行受影响)
*/
/*
为3的时候
role_popedom
----------------------------------------------------------------------------------------------------
1,3,5,11,13,15
2,3,5,12,16,17,18
(2 行受影响)
*/
drop table A,B
select b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',','+role_popedom+',')>0
and role_id='1'
/*
role_popedom
------------------
1,3,5,11,13,15
*/
--上面的不要，用这个
select distinct b.role_popedom from a,b,master..spt_values
where type='p' and charindex(',',','+role_id,number)=number
and charindex(','+substring(role_id,number,charindex(',',role_id+',',number)-number)+',',','+role_popedom+',')>0
and role_id='1'
/*
role_popedom
------------------
1,3,5,11,13,15
*/
declare @str varchar(10)
set @str='2,3'
select * from B where patindex('['+@str+']',role_popedom)>0
Create function fn_check_repeat(@bm1 varchar(2000), @bm2 varchar(2000))
returns tinyint
-- 检查两个字符串中是否有重复的单项
-- 如 '001,002' 和 '002,003' 返回1
-- '001,002' 和 '004,003' 返回0
as begin
declare @p1 int
declare @p2 int
declare @bm varchar(2000)
set @p1 = 0
set @p2 = 0
set @bm = ''
set @bm1 = isnull(@bm1, '')
set @bm2 = isnull(@bm2, '')
if @bm1 = '' --空代表全部，则返回匹配
return 1
if @bm2 = '' -- 空代表全部，则返回匹配
return 1
-- @bm1转换格式为',001,002,003,'
if left(@bm1, 1) <> ','
set @bm1 = ',' + @bm1
if right(@bm1, 1) <> ','
set @bm1 = @bm1 + ','
-- @bm2转换格式为'001,002,003,'
if left(@bm2, 1) = ','
set @bm2 = substring(@bm2, 2, 255)
if right(@bm2, 1) <> ','
set @bm2 = @bm2 + ','
select @p1 = CHARINDEX(',', @bm2)
while(@p1 <> 1 and @p1 <> 0)
begin
set @bm = left(@bm2, @p1 - 1)
if CHARINDEX(',' + @bm + ',', @bm1) > 0
return 1
set @bm2 = substring(@bm2, @p1 + 1, 255)
select @p1 = CHARINDEX( ',', @bm2)
end
return 0
end
select * from 表A a, 表B b where dbo.fn_check_repeat(a.role_id, b.role_popedom) = 1
