create function [dbo].[StringSplit](@spliter nvarchar(10)) retrun @str nvarchar(max) as begin select @str = admin_duty from admininfo where admin_id=2 return end go没看到你@spliter用在什么地方.
select @str=isnull(@str+' ','')+admin_duty from admininfo where admin_id=2
----函数(分离字符串) create function [dbo].[StringSplit](@str nvarchar(max),@spliter nvarchar(10)) returns @tb table(ch nvarchar(256)) AS BEGIN DECLARE @Num int,@Pos int, @NextPos int SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@str)) BEGIN SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@str) + 1 INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))) SELECT @Pos = @NextPos+1 END return END ----查询 SELECT * FROM dbo.StringSplit('',',') 第一个参数应该是select admin_duty from admininfo where admin_id=2所返回的结果
为什么没传过去呢? 如果实在传不过去的话,可以在函数里面定义一个变量 declare @admin_duty nvarchar(500) select @admin_duty = admin_duty from admininfo where admin_id=2
用这个现成的吧./* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i intset @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr)while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) endif @inputstr <> '\' insert @temp values(@inputstr)return end go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
把原函数改成如下 create function [dbo].[StringSplit](@ID int,@spliter nvarchar(10)) returns @tb table(ch nvarchar(256)) AS BEGIN DECLARE @Num int,@Pos int, @NextPos int,@str nvarchar(500) select @str = isnull(@str,'')+stuff((select ','+admin_duty from admininfo where admin_id = @ID for xml path('') ),1,1,'') SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@str)) BEGIN SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@str) + 1 INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))) SELECT @Pos = @NextPos+1 END return END
begin
select @str = admin_duty from admininfo where admin_id=2
return
end
go没看到你@spliter用在什么地方.
create function [dbo].[StringSplit](@str nvarchar(max),@spliter nvarchar(10))
returns @tb table(ch nvarchar(256))
AS
BEGIN
DECLARE @Num int,@Pos int, @NextPos int
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
return
END ----查询
SELECT * FROM dbo.StringSplit('',',')
第一个参数应该是select admin_duty from admininfo where admin_id=2所返回的结果
为什么没传过去呢?
如果实在传不过去的话,可以在函数里面定义一个变量
declare @admin_duty nvarchar(500)
select @admin_duty = admin_duty from admininfo where admin_id=2
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split
create function [dbo].[StringSplit](@ID int,@spliter nvarchar(10))
returns @tb table(ch nvarchar(256))
AS
BEGIN
DECLARE @Num int,@Pos int, @NextPos int,@str nvarchar(500)
select @str = isnull(@str,'')+stuff((select ','+admin_duty from admininfo where admin_id = @ID for xml path('') ),1,1,'')
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SELECT @Pos = @NextPos+1
END
return
END