Split comma separated values in MS Sql

Posted: October 10, 2013 in SQL

Introduction

Manytimes there is requirement where we have to send values to Sql stored procedure using parameter. Suppose we have to store value from CheckListBox having 10 items to Database, One way is to run loop one by one and pass parameter to sql stored procedure, in this case system will connect to sql 10 times (in our example). Other way is to create a string with comma(‘,’) separated and pass it to sql stored procedure in single parameter and then split it into sql query.

For that we can use Split function and we can use the data in the way we want.

Split function of SQL Server is not in-built function,We have to add it manually in our Table-valued function as you can find the function below.

Using the Code

CREATE FUNCTION [dbo].[Split] 
(
     @InputString VARCHAR(8000),
     @Delimiter VARCHAR(50)
)
RETURNS @Items TABLE 
(
     Item VARCHAR(8000)
)
AS
BEGIN
     IF @Delimiter = ' '
     BEGIN
          SET @Delimiter = ','
          SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
     END
     IF (@Delimiter IS NULL OR @Delimiter = '')
     SET @Delimiter = ','
     --INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
     --INSERT INTO @Items VALUES (@InputString) -- Diagnostic
     DECLARE @Item VARCHAR(8000)
     DECLARE @ItemList VARCHAR(8000)
     DECLARE @DelimIndex INT
     SET @ItemList = @InputString
     SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
     WHILE (@DelimIndex != 0)
     BEGIN
          SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
          INSERT INTO @Items VALUES (@Item)
          -- Set @ItemList = @ItemList minus one less item
          SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
     END -- End WHILE
     IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
     BEGIN
          SET @Item = @ItemList
          INSERT INTO @Items VALUES (@Item)
     END
     -- No delimiters were encountered in @InputString, so just return @InputString
     ELSE INSERT INTO @Items VALUES (@InputString)
     RETURN
     END -- End Function

How to Use ?

Create above function in MS SQL, it is a Table Valued Function.

Ex. if there are 10 items like item1, item2,……item10 then

SELECT item FROM dbo.split(‘item1,item2,item3,item4,item5,item6,item7,item8,item9,item10′,’,’)

Output will be

item1
item2
item3
item4
item5
item6
item7
item8
item9
item10

Screenshot

Function to Split comma separated values in MS Sql

Function to Split comma separated values in MS Sql

Advertisements

Leave a Reply

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