Adding items in comma-separated string into separate rows in SQLServer

Hi,

In this post, I’ll show how to insert a items in a comma-separated string into separate rows in a table. Consider for example we have a comma-separated string such as “amogh, anish, anvesh, uday”. after inserting into the table, the output should be like:

 

Expected Result

 

I have written a stored procedure which will take the comma-separated string as input and insert  a new row into the table for each item in the string.

 

The Stored procedure is as follows:

 

CREATE PROCEDURE AddCommaSeparatedUsersToTable

(

      @UserNames NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @DELIMITER NCHAR(1)   –delimiter used to separate the usernames

DECLARE @tmpUserNames NVARCHAR(MAX)

SET @tmpUserNames = @UserNames

SET @DELIMITER = ‘,’                    –Delimiter is a comma

DECLARE @commaIndex INT    

DECLARE @singleUserName NVARCHAR(MAX)               —singleUserName is the variable which holds each item in the comma-separated string

SELECT @commaIndex = 1    

      IF LEN(@tmpUserNames)<1 OR @tmpUserNames IS NULL  RETURN    

WHILE @commaIndex!= 0    

BEGIN    

      SET @commaIndex= CHARINDEX(@DELIMITER,@tmpUserNames)    

      IF @commaIndex!=0    

            SET @singleUserName= LEFT(@tmpUserNames,@commaIndex– 1)    

      ELSE    

            SET @singleUserName = @tmpUserNames    

      IF(LEN(@singleUserName)>0)

      BEGIN                        

            INSERT INTO SampleUserTable

            (

                  UserName

            )

            VALUES

            (

                  @singleUserName

            )

      END

      SET @tmpUserNames = RIGHT(@tmpUserNames,LEN(@tmpUserNames) – @commaIndex)    

      IF LEN(@tmpUserNames) = 0 BREAK    

END

END

 

This procedure will insert each item in the comma-separated string (UserNames, given as input parameter to the procedure) into the table “SampleUserTable” in separate rows.

 

Hope this helps!! 🙂

Advertisements

About Amogh Natu

Technology enthusiast, Associate Consultant @ Microsoft, music lover, love my guitar, Microsoft.NET, ASP.NET, C#.NET Professional.
This entry was posted in CodeProject, General, SQL Server and tagged , , , , . Bookmark the permalink.

One Response to Adding items in comma-separated string into separate rows in SQLServer

  1. Pingback: Retrieving column values as a comma separated string in SQLServer « Amogh Natu's Blog

Your comments or opinions.....

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s