Archive

Archive for September, 2012

Split Comma Separated values

September 6, 2012 1 comment

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
%d bloggers like this: