Splitting String in SQL Server

SQL SERVER 2016


SELECT * FROM STRING_SPLIT('John,Jeremy,Jack',',')

STRING_SPLIT is built - in - function sql server 2016 and you can directly use it.


If SQL SERVER is Below 2016 then

Write user defined function which returns tabular value like below

CREATE FUNCTION [dbo].[SplitString]
(    
      @strString NVARCHAR(MAX),
      @splitCharacter CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(strString , LEN(strString ) - 1, LEN(@strString)) <> @splitCharacter 
      BEGIN
            SET @strString = @strString + @splitCharacter 
      END

      WHILE CHARINDEX(@splitCharacter  @strString) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@splitCharacter , @strString)
           
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@strString, @StartIndex, @EndIndex - 1)
           
            SET @strString = SUBSTRING(@strString, @EndIndex + 1, LEN(@strString))
      END

      RETURN
END


And after that, use below command to get the splitted string.


SELECT * FROM [SplitString]('John,Jeremy,Jack',',')