Header add

 This article explain how to create a function that will split a string by comma, space or some other special characters into table Rows in SQL Server.

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', ',')



Post a Comment

Previous Post Next Post