SQL Delimited String

To create a function:

CREATE FUNCTION[dbo].[Split](@str NVARCHAR(Max),@spliter NVARCHAR(Ten))--@str: Target string--@spliter: DelimiterRETURNS @tb TABLE(ChNVARCHAR(Max)) asBEGINDECLARE @Num INT,@Pos INT,@NextPos INTSET @Num = 0SET @Pos = 1 while(@Pos <= LEN(@str))BEGINSELECT @NextPos = CHARINDEX(@spliter,@str,@Pos)IF(@NextPos = 0 OR @NextPos  is NULL)SELECT @NextPos = LEN(@str)+ 1INSERT  into @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str,@Pos,@NextPos - @Pos))))SELECT @Pos = @NextPos+1ENDRETURNENDGO

How to use:

SELECT *  from Dbo.split ('1,2,3,4',','--  Note: Multi-row single-column data tables 

Test statement:

IF object_id(N'[dbo]. [Split]') is  not NULL --Check the function [dbo]. [Split] "Is thereBEGIN    DROP FUNCTION [dbo].[Split] --Delete a functionENDGOCREATE FUNCTION[dbo].[Split](@str NVARCHAR(Max),@spliter NVARCHAR(Ten))--@str: Target string--@spliter: DelimiterRETURNS @tb TABLE(ChNVARCHAR(Max)) asBEGINDECLARE @Num INT,@Pos INT,@NextPos INTSET @Num = 0SET @Pos = 1 while(@Pos <= LEN(@str))BEGINSELECT @NextPos = CHARINDEX(@spliter,@str,@Pos)IF(@NextPos = 0 OR @NextPos  is NULL)SELECT @NextPos = LEN(@str)+ 1INSERT  into @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str,@Pos,@NextPos - @Pos))))SELECT @Pos = @NextPos+1ENDRETURNENDGOSELECT *  fromDbo.split ('1,2,3,4',',')DROP FUNCTION [dbo].[Split]
GO

Description

1, only support text string separation!!!

SQL Delimited String