--**************************************
--     
-- Name: Separating Function
-- Description:We often have a string of
--      numbers separated by a delimiter and ar
--     e required to break up the string into i
--     ndividual numbers for further use in cod
--     e.This function emulates the split funct
--     ion.
-- By: Jayanthi Venugopal
--
-- Inputs:The String of Numbers, identif
--     ying delimiter
--
-- Returns:The set of individual Numbers
--     
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=787&lngWId;=5--for details.--**************************************
--     

CREATE PROCEDURE Split(@string varchar(2000),@delimiter varchar(10))
as
	DECLARE @str varchar(2000)
	SET @str = @string
	DECLARE @d varchar(10)
	SET @d = @delimiter
	DECLARE @start int
	DECLARE @len int 
	DECLARE @pos int
	DECLARE @pos1 int
	DECLARE @val int
	SET @pos = 1
	SET @start = charindex(@d,@str,@pos)
	SET @len = len(@str)
	CREATE TABLE #temp (val int)
	WHILE @start <= @len
	BEGIN
		IF (@pos = 1) 
		BEGIN
			SET @val = substring(@str,1,@start-1)
			INSERT INTO #temp values(@val)
			SET @pos = @pos + 1
		END
		ELSE
		BEGIN
			SET @pos1 = charindex(@d,@str,@start+1)
			IF @pos1 = 0
			BEGIN
				 SET @val = substring(@str,@start+1,@len-@start)
				INSERT INTO #temp values(@val)
				break;
			END
			SET @val = substring(@str,@start+1,@pos1-@start-1)
			INSERT INTO #temp values(@val)
			SET @start = 	@pos1	
			SET @pos1 = 0
		END
	END
	SELECT * FROM #temp
	DROP TABLE #temp