MsSQL Split Function

The following demonstrates how to do a string Split using a character delimiter.

Edited: [dbo].[Split_PerformanceEnhanced] added - better performance results (31 July 2017).

--SQL function to do split (performance enhanced)
CREATE FUNCTION [dbo].[Split_PerformanceEnhanced]
(
 @List NVARCHAR(MAX),
 @Delimiter VARCHAR(5)
)  
RETURNS @RtnValue TABLE
(
 ID INT IDENTITY(1,1),
 Data VARCHAR(MAX)
) 
AS
BEGIN
 INSERT INTO @RtnValue(Data)
  SELECT [Value] FROM 
    ( 
    SELECT 
     [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
     CHARINDEX(@Delimiter, @List + @Delimiter COLLATE Latin1_General_100_BIN2, [Number]) - [Number])))
    FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
     FROM sys.all_objects) AS x
     WHERE Number <= LEN(@List)
     AND SUBSTRING(@Delimiter + @List, [Number], LEN(@Delimiter)) = @Delimiter
    ) AS y

 RETURN;
END
GO
--Test out the SQL function
SELECT * FROM dbo.Split_PerformanceEnhanced('one|two|three|four', '|')
GO

Below SQL function still works but prefer the above new performance enhanced version.

--SQL function to do split
CREATE FUNCTION [dbo].[Split]
(
 @RowData NVARCHAR(MAX),
 @SplitOn CHAR(1)
)  
RETURNS @RtnValue TABLE
(
 ID INT IDENTITY(1,1),
 Data VARCHAR(MAX)
) 
AS
BEGIN
 DECLARE @count INT
 SET @count = 1

 WHILE (CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) > 0)
 BEGIN
  INSERT INTO @RtnValue (data)
  SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) - 1)))
  SET @RowData = SUBSTRING(@RowData, CHARINDEX(@SplitOn COLLATE Latin1_General_100_BIN2, @RowData) + 1, LEN(@RowData))
  SET @count = @count + 1
 END

 INSERT INTO @RtnValue (data)
 SELECT Data = LTRIM(RTRIM(@RowData))

 RETURN
END
GO
--Test out the SQL function
SELECT * FROM dbo.Split('one|two|three|four', '|')
GO