Sql 字符串按指定字符转多行（表值函数）

Sql 字符串按指定字符转多行（表值函数）

 

方法一

select * from SplitToTable('12+3+4545+2322','+')
select * from SplitToTable('12,3,4545,2322',',')

ALTER FUNCTION [dbo].[SplitToTable]
(
@SplitString nvarchar(max), --拆解字符串
@Separator nvarchar(10)=' '  --拆解标识（根据什么字符拆解）
)
RETURNS @SplitStringsTable TABLE  --返回表结构
(
[value] nvarchar(max)
)
AS
BEGIN

insert into @SplitStringsTable
SELECT b.strString
FROM(
select strString=CONVERT(XML,'<root><v>'+replace(RTRIM(LTRIM(@SplitString)),@Separator,'</v><v>')+'</v></root>')
) a
OUTER APPLY(
SELECT strString = C.v.value('.','NVARCHAR(MAX)')
FROM a.strString.nodes('/root/v') C(v)
) b
RETURN;
END

 

方法二

select * from SplitToTable('12+3+4545+2322','+')
select * from SplitToTable('12,3,4545,2322',',')  

ALTER 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