Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server 2005, 2008,2012 and 2016

This article explain how to split a function in SQL Server of version 2005, 2008,2012 and 2016

SplitString function

The SplitString function is a Table-Valued function i.e. it returns a Table as output and accepts two parameters namely:

 @Input – The string to be split.

 @Character – The character that is used to separate the words or letters.

CREATE FUNCTION [dbo].[SplitString]

(    
      @Input NVARCHAR(MAX),
	  @Character CHAR(1)
)
 
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
 
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
	  SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
 
            SET @EndIndex = CHARINDEX(@Character, @Input)
 
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
 
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
 
      END
      RETURN
END
 

The following SQL query split the string separated (delimited) by comma.

SELECT item FROM dbo.SplitString('HR,Accounting,Finance,IT', ',')