Easy way to split string

We generally use function to split a delimited string into rows. The code for same is listed below

CREATE FUNCTION
 [dbo].[fnSplitString] (
 @string NVARCHAR(MAX)
 ,@delimiter CHAR(1)
 )
RETURNS@output  
TABLE (splitdata NVARCHAR(MAX))

BEGIN
 DECLARE @start INT
  ,@end INT

 SELECT @start = 1
  ,@end = CHARINDEX(@delimiter, @string)

 WHILE @start < LEN(@string) + 1
 BEGIN
  IF @end = 0
   SET @end = LEN(@string) + 1

  INSERT INTO @output (splitdata)
  VALUES (SUBSTRING(@string, @start, @end - @start))

  SET @start = @end + 1
  SET @end = CHARINDEX(@delimiter, @string, @start)
 END

 RETURN
END GO

We will be using XQuery method to split the string in easy and efficient way.
Following steps are taken to split the string.

#1. Convert the string into formatted XML, 
for that replace the delimitor with any tag (used in example).
#2. Remove empty nodes like '' or ''
#3. list value of all nodes 


USE tempdb

DECLARE @x VARCHAR(MAX)
DECLARE @x1 XML
DECLARE @t1 DATETIME
DECLARE @t2 DATETIME

SET @x = '100|101|102|'
SET @x = replicate(@x, 1)

SELECT @x1 = cast
(replace
(replace
('<x>' + replace
(@x, '|', '</x><x>')
 + '</x>', '<x ></x>', '')
, '<x></x>', '')
 AS XML)


SET @t1 = getdate() --  start time of process

SELECT X.x1.value('.', 'varchar(max)')
FROM @x1.nodes('//x') X(x1) -- XQuery

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2) -- Find time difference in milliseconds
SET @t1 = getdate() --  start time of process

SELECT *
FROM [dbo].[fnSplitString](@x, '|')

SET @t2 = getdate() --  End time of process

SELECT datediff(ms, @t1, @t2)

Benchmarks for XQuery method to split the string
 -- XQuery
 -- 0ms for 3
 -- 0ms for 30
 -- 6ms for 300
 -- 36ms for 3000
 -- 350ms for 30000
 -- 7s for 300000

Benchmarks for Procedure method to split the string
 -- 3ms for 3
 -- 10ms for 30
 -- 100ms for 300
 -- Procedure 1m for 3000
 -- Procedure 11s for 30000
 -- Procedure 2m15s for 300000

Conclusion: Looking at performance and simplicity of XQuery, I will recommend it over procedural way. Hope it helps.