Home > T-SQL > Split Comma Separated values

Split Comma Separated values


In this thread I would like to share my SQL scripts to split the comma separate values in to  individual values. This script can be effectively used when passing multivalue parameters from the SSRS report to stored procedures.

TSQL – CODE:

DECLARE @text VARCHAR (50)

DECLARE @Delimiter CHAR (1)

DECLARE @length INT

DECLARE @table TABLE

(

value VARCHAR(50 )

)

SET @text = '1,2,4,887,77889,1,132,4,5,455,4478'

SET @Delimiter = ','

SET @length = Len( @text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter , @text) <> 0 )

BEGIN

INSERT INTO @table(value )

SELECT LEFT( @text, Charindex(@Delimiter , @text) - 1 )

SET @text = RIGHT(@text , Len (@text) - Charindex(@Delimiter , @text))

SET @length= Len(@text )

END

ELSE

BEGIN

INSERT INTO @table(value )

SELECT @text

SET @length = 0

END

END

SELECT *

FROM   @table

Result:

image_thumb

This can be also created as a function in SQL Server as shown below,

CREATE FUNCTION dbo.Split(@text      VARCHAR (MAX),

@Delimiter CHAR (1))

RETURNS @table TABLE (VALUE VARCHAR(50))

AS

Begin

DECLARE @length INT

SET @length = Len(@text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter, @text) <> 0 )

BEGIN

INSERT INTO @table(value)

SELECT LEFT(@text, Charindex(@Delimiter, @text) - 1)

SET @text = RIGHT(@text, Len (@text) - Charindex(@Delimiter, @text))

SET @length= Len(@text)

END

ELSE

BEGIN

INSERT INTO @table(value)

SELECT @text

SET @length = 0

END

END

RETURN

END;

To use the function, execute below code,

SELECT * FROM dbo.SPLIT ( '1,2,4,887,77889,1,132,4,5,455,4478',',') 

OUTPUT:

c

Advertisements
  1. No comments yet.
  1. January 26, 2015 at 5:14 pm

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

%d bloggers like this: