Hi! I have a column which needs to store multiple id values: like 1,2,3.

I'm trying to write a table function that will separate the values by the comma, but I'm not sure if I'm going in the right direction.

Any guidance?

Thanks 

You can write a customized SPLIT function to separate them in SQL but this is not a good design.

Try to modify your table to hold single value for this column. It will be a lot easy to do different queries later.

I'm no database expert, but don't do it.  You don't want to store data like that.  Design your tables differently so you can store each value as a seperate row, maybe using a lookup table as a foreign key.

I have a table 'sessions' and another table called 'groups' 

in the session table I have: sessionID, semester, date, time, and groupID

a few groups can attend each session, so in the groupID column I want to insert a few id's (rather than having the same information repeated for each group.)

before i continue with this SPLIT function, do you have any other ideas of how to store this?

You can use a link table SeesionGroups (SeeionID, GroupID) to link both tables with their ids. 

If you're sure you want to store multiple values in a single column, a native XML column can be a good approach.  That way, you can use XQuery query it, parse it, etc.  Much better than commas.

Hi,

As Limno suggested , yes it is not good store values like that in a column as far as normalization point of view, but if you are storing here is the test code to split the values

Go

CREATE FUNCTION [fn_Split](@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')
RETURNS
@Strings TABLE ( [position] INT IDENTITY PRIMARY KEY , [value] varchar(100))
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN -- Find the first delimiter
SET @index = CHARINDEX(@delimiter , @text)
--No delimiter left?
--Insert the remaining @text and break the loop
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings
VALUES (CAST(@text AS varchar(100)))
BREAK
END
-- Found a delimiter
--Insert left of the delimiter and truncate the @text
IF (@index > 1)
BEGIN
INSERT INTO @Strings
VALUES (CAST(LEFT(@text, @index - 1) AS varchar(100)))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END --Delimiter is 1st position = no @text to insert
ELSE
SET @text = CAST(RIGHT(@text, (LEN(@text) - @index)) AS varchar(100))
END RETURN
END

GO

--To Test It

GO

DECLARE @commasprStr AS VARCHAR(50)
SET  @commasprStr='Abc,Def,Ghi,Jkl'
SELECT  * FROM  [dbo].[fn_Split](@commasprStr,',')

GO

--OUTPUT

--position    value
------------- ---------
--1           Abc
--2           Def
--3           Ghi
--4           Jkl

This version of the function (SQL Server 2005 and up) would be more performant

USE [AllTests]
GO

/****** Object:  UserDefinedFunction [dbo].[fnSplit]    Script Date: 11/07/2009 23:34:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Test query
CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),
 @delim CHAR(1) = ','
) RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )
  SELECT LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0
GO

See also this thread http://forum.lessthandot.com/viewtopic.php?f=17&t=7566&st=0&sk=t&sd=a&hilit=split