Sql Server Split Function

Fastest split function.

DECLARE @String VARCHAR(MAX),
        @xml AS xml,
        @Delimiter AS VARCHAR(10)
DECLARE @TEMP TABLE (LocID INT, Location VARCHAR(50))

INSERT INTO @Temp
SELECT 9, 'Chennai' UNION ALL
SELECT 13, 'Salem' UNION ALL
SELECT 42, 'Idappadi' UNION ALL
SELECT 32, 'Poolampatti' UNION ALL
SELECT 11, 'Chitoor'

SET @String    = '13,42,47,32,11,,9'
SET @Delimiter =','
SET @xml       = CAST((''+REPLACE(@String, @Delimiter, '')+'') AS XML)

--Split the string
SELECT   N.value('.', 'VARCHAR(MAX)') AS LocationID
FROM     @xml.nodes('x')              AS T ( N )
WHERE    N.value('.', 'VARCHAR(MAX)') <> ''
ORDER BY CAST(N.value('.', 'VARCHAR(MAX)') AS INT)

--How to use it in join with a table
SELECT     LocID,
           Location
FROM       @Temp
           INNER JOIN @xml.nodes('x') AS T ( N )
           ON         N.value('.', 'VARCHAR(MAX)') = LocID

Reference

By using the above batch of query, we can pass ,(comma) separated values as XML from the application.
The size doesn't matter, when we pass the variable as XML from the application,
This is very small query, so no need to write it in a function.
This increase the performance of the query.

--Split Function

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

-----------------------------------------------------------------------------Split Function With Identity Colum

CREATE FUNCTION [dbo].[Split](@String NVARCHAR(4000), @Delimiter CHAR(1))
RETURNS @Results TABLE (id int identity, Items NVARCHAR(4000))
AS
BEGIN
DECLARE @index INT
DECLARE @slice NVARCHAR(4000)

SELECT @index = 1
IF @String is null RETURN
BEGIN
WHILE @index != 0
BEGIN

SELECT @index = CHARINDEX(@Delimiter,@String)
IF @index !=0
BEGIN
SELECT @slice = left(@String,@index - 1)
END
ELSE
BEGIN
SELECT @slice = @String
END

INSERT INTO @Results(Items) VALUES(@slice)
SELECT @String = right(@String,LEN(@String) - @index)
IF LEN(@String) = 0 BREAK

END RETURN
END
END

---------------------------------------------------------------------------
--Simple split function in www.sqlteam.com

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[Split] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) 
AS Item  FROM Pieces
)