--將@vchString字符串以@vchSplit為分割符分割成記錄集 --progress99 -- 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
progress99(如履薄冰) 你的这个函数怎么用阿
SELECT * FROM dbo.FunSplitStringToAraay('28,353,2,35,88',',')? 你里面的循环好像不对吧,我用查询分析器生成的结果是 28 353 2 35而没有88 也就是说循环除了问题,没有把88写到临时表里面
改了一下 --將@vchString字符串以@vchSplit為分割符分割成記錄集 --progress99 -- 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 Set @vchSubstring=Reverse(left(Reverse(@vchString),CharIndex(@vchSplit,Reverse(@vchString))-1)) INSERT INTO @tabArray(string) SELECT @vchSubstring RETURN END
context="aaa#bbb#ccc" arrstr=split(context,"#") for i=0 to ubound(arrstr) response.write arrstr(i) next
改一下: 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 SELECT @vchSubstring=SUBSTRING(@vchString,@intStart,len(@vchString)) INSERT INTO @tabArray(string) SELECT @vchSubstring RETURN END
Create function funcGetStrBySplit(@sSource varchar(4000),@cSplit varchar(2)) returns @t table(cc varchar(50)) as begin insert @t select name from sysobjects where 1=2 declare @cStr_b varchar(4000) select @cStr_b=@sSource while(charindex(@cSplit,@cStr_b,1)>0) begin insert @t select substring(@cStr_b,1,charindex(@cSplit,@cStr_b,1)-1) select @cStr_b=substring(@cStr_b, charindex(@cSplit,@cStr_b,1)+1, len(@cStr_b)-charindex(@cSplit,@cStr_b,1)) end if @cStr_b<>'' insert @t select @cStr_b return end
要数组的话,那是在程序中根据“#”符号进行判断割成数组就行了。 这个很难吗?
在数据库把一个数组就当一个字符串处理存贮就行了。
--progress99
-- 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
你里面的循环好像不对吧,我用查询分析器生成的结果是
28
353
2
35而没有88
也就是说循环除了问题,没有把88写到临时表里面
--將@vchString字符串以@vchSplit為分割符分割成記錄集
--progress99
-- 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
Set @vchSubstring=Reverse(left(Reverse(@vchString),CharIndex(@vchSplit,Reverse(@vchString))-1))
INSERT INTO @tabArray(string) SELECT @vchSubstring
RETURN
END
arrstr=split(context,"#")
for i=0 to ubound(arrstr)
response.write arrstr(i)
next
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
SELECT @vchSubstring=SUBSTRING(@vchString,@intStart,len(@vchString))
INSERT INTO @tabArray(string) SELECT @vchSubstring
RETURN
END
returns @t table(cc varchar(50))
as
begin
insert @t select name from sysobjects where 1=2
declare @cStr_b varchar(4000)
select @cStr_b=@sSource
while(charindex(@cSplit,@cStr_b,1)>0)
begin
insert @t select substring(@cStr_b,1,charindex(@cSplit,@cStr_b,1)-1)
select @cStr_b=substring(@cStr_b,
charindex(@cSplit,@cStr_b,1)+1,
len(@cStr_b)-charindex(@cSplit,@cStr_b,1))
end
if @cStr_b<>''
insert @t select @cStr_b
return
end