Comma Separated Values to Tables

Many people ask me whether a set of values provided from a Frone End Application can be compared acrossed a column value in the DB.

Ideal example would be we have a multi select List Box and user selects more than one value and clicks on search now the values he selected should be compared to a column using 'IN' clause but we never get to know how many value user chooses in the list box.To solve this use this function below


CREATE FUNCTION fn_colstoRows (@InputSTR VARCHAR(5000))
RETURNS @ROWS TABLE ( ROWVALUE VARCHAR(5000))
AS
BEGIN
DECLARE @iPosition INT
SELECT @iPosition = 1
DECLARE @iPrevPosition INT

WHILE CHARINDEX(',',@InputSTR,@iPosition) > 0
BEGIN

INSERT INTO @ROWS
SELECT SUBSTRING(@InputSTR,@iPosition,CHARINDEX(',',@InputSTR,@iPosition)-1)

SELECT @InputSTR = SUBSTRING(@InputSTR,CHARINDEX(',',@InputSTR,@iPosition)+1,LEN(@InputSTR))

END

INSERT INTO @ROWS
SELECT @InputSTR

RETURN
END

On Executing this using the below statement
select * from dbo.fn_colstorows('Value1,Value25,Value3')

Output is as follows

ROWVALUE
-------------------
Value1
Value25
Value3

So now we converted a comma separated 3 columns to 3 rows