--**************************************
--
-- 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
|
