BLOG

Split function in SQL Server Example: Function to Split Comma separated (Delimited) string in SQL Server

01-05-2019  0 Comment(s)

 

The Split string function
The following script has to be executed in your database. 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.

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

Comment Here

Comments

No Comments to Show

WE ALWAYS WORK WITH :