Create procedure sp_TestParseArray
( 
	@Array varchar(1000),
	@ItemSeparator char(1),
	@PairSeparator char(1)

) 

AS

' SET DEMO DATA
SET @Array = 'one,1#two,2#three,3'
SET @ItemSeparator = ','
SET @PairSeparator = '#'

-- Created by graz@sqlteam.com, modified by Jeepaholic
set nocount on
-- @Array is the array we wish to parse
-- @ItemSeparator is the separator charactor such as a comma for each item in the pair
-- @PairSeparator is the separator character for each pair

declare @ItemSeparatorPosition int -- This is used to locate each separator character
declare @PairSeparatorPosition int -- This is used to locate each separator character
declare @ArrayValue varchar(1000) -- this holds each array value as it is returned
declare @ArrayKey varchar(1000) -- this holds the Key portion of the pair
declare @ArrayItem varchar(1000) -- this holds the Value portion of teh pair

-- For my loop to work I need an extra separator at the end.  I always look to the
-- left of the separator character for each array value
set @Array = @Array + @PairSeparator

-- Loop through the string searching for Pair separtor characters
while patindex('%' + @PairSeparator + '%' , @Array) <> 0 
begin

  -- patindex matches the a pattern against a string
  set @PairSeparatorPosition =  patindex('%' + @PairSeparator + '%' , @Array)
  set @ArrayItem = left(@Array, @PairSeparatorPosition - 1)

	set @ItemSeparatorPosition = patindex('%' + @ItemSeparator + '%', @ArrayItem)
	Set @ArrayKey = left(@ArrayItem, @ItemSeparatorPosition -1)
	Set @ArrayValue = right(@ArrayItem, len(@ArrayItem)- @ItemSeparatorPosition)

  -- This is where you process the values passed.
  -- Replace this select statement with your processing
	SELECT @ArrayKey as [Key], @ArrayValue as [Value]

  -- This replaces what we just processed with and empty string
  set @Array = stuff(@Array, 1, @PairSeparatorPosition, '')
end

set nocount off
go