T-SQL 截取字符串到table
今天自己遇到个问题,随手写了点东西,觉得挺实用,拿出来跟大家分享!
--将以逗号格开的字符串 用指定字符或符号格开转成TABLE
create FUNCTION [dbo].[SplitToTable]
(
@SplitString nvarchar(max), --截取目标字符串
@Separator nvarchar(10)=' ' --间隔字符
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO