Create Split Function in Sql

You have Comma separated Document and you want to split data and store into Table that time you have to create Split Function.

Steps to Create Split Function in Sql server

Step-1:- Go to Database and Expand it.

Step-2:- Go to Programmability, Expand it and Expand Functions and Go to Table-Valued Functions.

Step-3:- Create New Multiline Fuction Option.

Step-4:- Replace Code With Below Code

CREATE FUNCTION [dbo].[SplitString] 

(

-- Add the parameters for the function here

@Input NVARCHAR(MAX),

    @Character CHAR(1)

)

RETURNS @Output TABLE(

SplitTable nvarchar(1000)

)

AS

BEGIN

-- Declare the return variable here

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(SplitTable)

            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

           

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

      RETURN

END

Step-5:- After Executing Above Command, You can Find Out dbo.SplitString Function in Table-valued Function Panel. Your Split Function Successfully Created.

Step-6:- Now to Execute or Use SplitString Function using Following Command

SELECT SplitTable FROM SplitString('@InputString','@Separate Character');

For Example,

SELECT SplitTable FROM SplitString('Brij,Patel,TSE,Computer',',') Executing this Command.