SQL Server字符串切割函数
发布时间
阅读量：
阅读量
SQL Server中把一个字符串切割成多个数据函数
CREATE FUNCTION SplitStringToTable
(
in Input VARCHAR(MAX(VARCHAR)) , -- 输入切割的字符串
in Separator VARCHAR(MAX(VARCHAR)) = ',' -- 切割标识
)
RETURNS out TABLE TABLE
(
Id INT IDENTITY(1, 1) , -- 返回一个表有id,value
result VARCHAR(MAX)
)
AS
BEGIN
DECLARE @RemoveEmptyEntries BIT = 1
DECLARE @Index INT ,
@Entry VARCHAR(MAX)
SET @Index = CHARINDEX(@Separator, @Input)
WHILE ( @Index > 0 )
BEGIN
SET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index - 1)))
IF ( @RemoveEmptyEntries = 0 )
OR ( @RemoveEmptyEntries = 1
AND @Entry <> ''
)
BEGIN
INSERT INTO @TABLE
( [Value] )
VALUES ( @Entry )
END
SET @Input = SUBSTRING(@Input,
@Index + DATALENGTH(@Separator) / 2,
LEN(@Input))
SET @Index = CHARINDEX(@Separator, @Input)
END
SET @Entry = LTRIM(RTRIM(@Input))
IF ( @RemoveEmptyEntries = 0 )
OR ( @RemoveEmptyEntries = 1
AND @Entry <> ''
)
BEGIN
INSERT INTO @TABLE
( [Value] )
VALUES ( @Entry )
END
RETURN
END函数调用
select * from SplitStringToTable('AB/C/ASE','/')
全部评论 (0)
还没有任何评论哟~