SQL的 Split函数

表 BD_Employe

ID	name
1	A1
2	A2
3	A3

 

表 bd_mfc 

ID	NAme	DLManage_C	isdl
1	mfc1	1,2,3	1
2	mfc2		0
3	mfc3	2,3	1

 

 

create function f_split(@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin

while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
go
DECLARE @str VARCHAR(200)=''
SET @str=(SELECT TOP 1 DLManage_C FROM bd_mfc WHERE isdl=1 and id =1)
SELECT * FROM BD_Employee WHERE rtrim(id) IN(
select * from dbo.f_split(@str,',')
)
drop function f_split