22,209
社区成员
发帖
与我相关
我的任务
分享
USE DBTest
GO
CREATE FUNCTION [dbo].[Split]
(
@Input varchar(4000), -- 输入字符串
@Separator varchar(4000) = '.' -- 分隔符
)
RETURNS
@Result TABLE
(
Value varchar(4000)
)
AS
BEGIN
DECLARE @InputLength int;
SET @InputLength = LEN(@Input);
DECLARE @SeparatorLength int;
SET @SeparatorLength = LEN(@Separator);
DECLARE @LastIndex int;
SET @LastIndex = 0;
DECLARE @CurrentIndex int;
SET @CurrentIndex = -1;
WHILE @CurrentIndex != 0
BEGIN
SET @CurrentIndex = CHARINDEX(@Separator, @Input, @LastIndex );
IF @CurrentIndex != 0
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @CurrentIndex - @LastIndex)
)
ELSE
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @InputLength + 1 - @LastIndex)
)
SET @LastIndex = @CurrentIndex + @SeparatorLength;
END
RETURN
END
GO
select dbo.Split('12,2,3,4,5,6,7',',')
ALTER FUNCTION [dbo].[Split]
(
@Input varchar(4000), -- 输入字符串
@Separator varchar(4000) = '.' -- 分隔符
)
RETURNS @Result TABLE
(
[Value] varchar(4000)
)
AS
BEGIN
DECLARE @i INT
SELECT @i=CHARINDEX(@Separator,@Input)
WHILE @i>0
BEGIN
INSERT @Result VALUES(LEFT(@Input,@i-1))
SELECT @Input=STUFF(@Input,1,@i,''),@i=CHARINDEX(@Separator,@Input)
END
INSERT @Result VALUES(@Input)
RETURN
END
GO
select * FROM dbo.[Split]('12,2,3,4,5,6,7',',')
/*
12
2
3
4
5
6
7
*/
select * from dbo.Split('12,2,3,4,5,6,7',',')
--你是个表值函数 所以需要像访问表那样访问
CREATE FUNCTION [dbo].[Split]
(
@Input varchar(4000), -- 输入字符串
@Separator varchar(4000) = '.' -- 分隔符
)
RETURNS
@Result TABLE
(
Value varchar(4000)
)
AS
BEGIN
DECLARE @InputLength int;
SET @InputLength = LEN(@Input);
DECLARE @SeparatorLength int;
SET @SeparatorLength = LEN(@Separator);
DECLARE @LastIndex int;
SET @LastIndex = 0;
DECLARE @CurrentIndex int;
SET @CurrentIndex = -1;
WHILE @CurrentIndex != 0
BEGIN
SET @CurrentIndex = CHARINDEX(@Separator, @Input, @LastIndex );
IF @CurrentIndex != 0
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @CurrentIndex - @LastIndex)
)
ELSE
INSERT @Result
(
Value
)
VALUES
(
SUBSTRING(@Input, @LastIndex, @InputLength + 1 - @LastIndex)
)
SET @LastIndex = @CurrentIndex + @SeparatorLength;
END
RETURN
END
GO
select * from [dbo].[Split]('12,2,3,4,5,6,7',',')
drop FUNCTION [dbo].[Split]
/*Value
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12
2
3
4
5
6
7
(7 行受影响)*/
select * from dbo.Split('12,2,3,4,5,6,7',',')
select [dbo].[Split]()