看看出這個函數--將@vchString字符串以@vchSplit為分割符分割成記錄集
-- DROP FUNCTION dbo.FunSplitStringToAraay
-- SELECT * FROM dbo.FunSplitStringToAraay('28,353,2,35,88',',')
CREATE FUNCTION dbo.FunSplitStringToAraay(@vchString varchar(1000),@vchSplit varchar(10))
RETURNS @tabArray table
(
string varchar(100)
)
AS
BEGIN
DECLARE @intStart int
DECLARE @intLocation int
DECLARE @vchSubstring varchar(100)
SELECT @intStart =1
SELECT @intLocation = CHARINDEX(@vchSplit,@vchString,@intStart)
WHILE (@intLocation <>0 )
BEGIN
SELECT @vchSubstring=SUBSTRING(@vchString,@intStart,@intLocation-@intStart)
INSERT INTO @tabArray(string) SELECT @vchSubstring
SELECT @intStart = @intLocation +1
SELECT @intLocation = CHARINDEX(@vchSplit,@vchString,@intStart)
END
RETURN
END調用:
SELECT * FROM dbo.FunSplitStringToAraay('28,353,2,35,88',',')
--返回
28
353
2
35
88
-- DROP FUNCTION dbo.FunSplitStringToAraay
-- SELECT * FROM dbo.FunSplitStringToAraay('28,353,2,35,88',',')
CREATE FUNCTION dbo.FunSplitStringToAraay(@vchString varchar(1000),@vchSplit varchar(10))
RETURNS @tabArray table
(
string varchar(100)
)
AS
BEGIN
DECLARE @intStart int
DECLARE @intLocation int
DECLARE @vchSubstring varchar(100)
SELECT @intStart =1
SELECT @intLocation = CHARINDEX(@vchSplit,@vchString,@intStart)
WHILE (@intLocation <>0 )
BEGIN
SELECT @vchSubstring=SUBSTRING(@vchString,@intStart,@intLocation-@intStart)
INSERT INTO @tabArray(string) SELECT @vchSubstring
SELECT @intStart = @intLocation +1
SELECT @intLocation = CHARINDEX(@vchSplit,@vchString,@intStart)
END
RETURN
END調用:
SELECT * FROM dbo.FunSplitStringToAraay('28,353,2,35,88',',')
--返回
28
353
2
35
88
/*
字符串分拆函数
--调用示例
select * from dbo.fsplit('a;adf;fd;ed;dh',';')
select * from dbo.fsplit('a,,adf,,fd,,ed,,dh',',,')
邹建 2003.07*/--检查函数是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fsplit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fsplit]
GO--创建字符串分拆的函数
create function fsplit(
@str varchar(8000) --要分拆的字符串
,@spli varchar(10) --字符串分隔符
)
returns @re table(istr varchar(8000))
as
begin
declare @i int,@splen int
select @splen=len(@spli)
,@i=charindex(@spli,@str)
while @i>0
begin
insert into @re values(left(@str,@i-1))
select @str=substring(@str,@i+@splen,8000)
,@i=charindex(@spli,@str)
end
if @str<>'' insert into @re values(@str)
return
end
drop function [dbo].[splitstring]
GOCREATE FUNCTION dbo.splitstring(@str varchar(4000),@c varchar(10))
RETURNS @t table(s varchar(100))
AS
BEGIN
DECLARE @s varchar(4000)
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(@c,@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
INSERT INTO @t(s) VALUES(@s)
RETURN
END
GO
--调用:
select * from dbo.SplitString('1000,1001,1002',',')
drop function [dbo].[splitstring]
GOCREATE FUNCTION dbo.splitstring(@str varchar(4000),@c varchar(10))
RETURNS @t table(s varchar(100))
AS
BEGIN
DECLARE @s varchar(4000)
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(@c,@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
INSERT INTO @t(s) VALUES(@s)
RETURN
END
GO
--调用:
select * from dbo.SplitString('1000,1001,1002',',')
-----------------------------------------------------------------
-- 按照用户ID取得某用户消息表信息
------------------------------------------------------------------CREATE PROCEDURE [Chinact_OA_GetMessageBySenderID]
(
@SenderID as [int]
)
ASSELECT
*
FROM
message a inner join employee b
on a.receivers=b.employeeID
WHERE
(
[SenderID]=@SenderID
)
GO我想把receivers里的用逗号分隔的数据库依次提取出来与employee里的employeeID分别对应,应该怎么实现啊,谢谢了!
-- 按照用户ID取得某用户消息表信息
------------------------------------------------------------------CREATE PROCEDURE [Chinact_OA_GetMessageBySenderID]
(
@SenderID as [int]
)
ASSELECT
*
FROM
message a inner join employee b
on ','+a.receivers+',' like '%,'+cast(b.employeeID as varchar)+',%'
WHERE
(
[SenderID]=@SenderID
)
GO
怎么对应???没理解
我是想这样:
最终显示的是Receivers
lordal,lyc,jyemployee中的name 100-lordal,1001-lyc,1002-jy同时我也谢谢其它人的回答,我也再想一想:)
EmployeeID DeptIDs DeptName Name ScreenName Password JobTitle
1000 2 技术部 张三 zs 123456 程序员
1001 3 财务部 李四 ls 456789 会计
1002 4 业务部 王五 ww 147258 业务员
1003 5 行政部 赵六 zl 546256 接线员
Message(短消息表)
MessageID SenderID MessageType Title Content SendDate Receivers
1 1003 通知 开会!全体员工大会! 2004-04-09 1000,1001,1002
想实现这样的功能:
MessageID SenderID MessageType Title Content SendDate Receivers
1 1003 通知 开会!全体员工大会! 2004-04-09 1000,1001,1002
EmployeeID DeptIDs DeptName Name ScreenName Password JobTitle
1000 2 技术部 张三 zs 123456 程序员
EmployeeID DeptIDs DeptName Name ScreenName Password JobTitle
1001 3 财务部 李四 ls 456789 会计
EmployeeID DeptIDs DeptName Name ScreenName Password JobTitle
1002 4 业务部 王五 ww 147258 业务员这就是根据EmployeeID与Receivers中用逗号分隔的ID相对应取得的结果。zjcxc(: 邹建 :) :谢谢你,昨天下班了,不好意思啊:)
create table Employee(EmployeeID int,DeptIDs int,DeptName varchar(10),Name varchar(10),ScreenName varchar(10),Password varchar(10),JobTitle varchar(10))
insert Employee select 1000,2,'技术部','张三','zs','123456','程序员'
union all select 1001,3,'财务部','李四','ls','456789','会计'
union all select 1002,4,'业务部','王五','ww','147258','业务员'
union all select 1003,5,'行政部','赵六','zl','546256','接线员'create table Message(MessageID int,SenderID int,MessageType varchar(10),Title varchar(10),Content varchar(50),SendDate datetime,Receivers varchar(50))
insert Message select 1,1003,'通知','开会!','全体员工大会!','2004-04-09','1000,1001,1002'
go--查询
declare @SenderID int
set @SenderID=1003 --要查询的SenderIDselect MessageID,SenderID,MessageType,Title,Content
,SendDate=convert(varchar(10),SendDate,120),Receivers
from Message where SenderID=@SenderID
union all
select a.*
from Employee a join Message b
on ','+b.Receivers+',' like '%,'+cast(a.EmployeeID as varchar)+',%'go--删除测试
drop table Employee,Message/*--测试结果
MessageID SenderID MessageType Title Content SendDate Receivers
----------- ----------- ----------- ---------- ------------------------------ -----------------
1 1003 通知 开会! 全体员工大会! 2004-04-09 1000,1001,1002
1000 2 技术部 张三 zs 123456 程序员
1001 3 财务部 李四 ls 456789 会计
1002 4 业务部 王五 ww 147258 业务员(所影响的行数为 4 行)--*/