split function

How to use the Split function in SQL Server?

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

  1. @Input – The string to be split.
  2. @Character – The character that is used to separate the words or letters.
    Creating Function:
    CREATE FUNCTION 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
    GO
    Ex:
    SELECT Item
    FROM dbo.SplitString(‘Apple,Mango,Banana,Guava’, ‘,’)

Leave a comment

Your email address will not be published. Required fields are marked *