SQL Split function to split an input string

This article I am starting with a real life example. In posting an article in a blog we have to define tags (Like  C#.NET, AJAX, ASP.NET,HTML) upon the article. We usually take these tags in a TextBox with separated them by comma(,). To insert these into database we have two ways to do.

First Method using C# :

Using C# you can split the TextBox items and use a for loop to insert into the database. Code is as follow.

string []tags = txtTags.Text.Trim().Split(',');

/* With for loop */
for (int i=0;i<tags.Count ;i++)
{
 /* perform db query with tags[i].ToSting();
}

/* with foreach */
foreach (string i in tags)
{
  /* perform db query with i.ToSting();
}

Second SQL Method :

Here in the SQL we pass the whole items of TextBox into SQL function to split it and then insert these into specific table. Lets see how to do this.

CREATE FUNCTION SplitText
(    
      @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

-- Create a temporary table to insert tags
create #tblTemp
(
Id identity (1,1),
tag nvarchar(50)
)

-- Inserting into tmpTable
insert into #tblTemp (temp) values 
SELECT Item FROM dbo.SplitText('ASP.NET,C#.NET,ADO.NET,JavaScript', ',')  
-- Seperated by Comma(,). Place any thing according to you.

Execute your SQL batch query to inserting the tags into table. CodeProject