Function
@listString VARCHAR(8000),
@Delimeter char(1)
RETURNS @ValueTable table
(
Value VARCHAR(8000)
)
AS
BEGIN
DECLARE @NextString VARCHAR(8000)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String VARCHAR(8000)
DECLARE @Delim VARCHAR(1)
SET @NextString = ''
SET @String = @listString
SET @Delim = @Delimeter
SET @String = @String + @Delim
SET @Pos = charindex(@Delim,@String)
WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
INSERT INTO @ValueTable (Value) Values (@NextString)
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delim,@String)
END
RETURN
END
GO
pass delimeter value as ':'.