Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Dead Link in an Article

Author  Topic 

Jeepaholic
Starting Member

36 Posts

Posted - 2003-06-24 : 12:58:34
The link labeled "code" that should provide a SP that parses an "array" into a database is dead. The article is here:

http://www.sqlteam.com/item.asp?ItemID=637

The dead link is here:
http://www.sqlteam.com/downloads/sp_parsearray.sql

Please help!

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-06-24 : 17:21:48
This is fixed. Thanks.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Jeepaholic
Starting Member

36 Posts

Posted - 2003-06-24 : 20:44:07
Awesome, thanks for the reply. FWIW, I modified it to handle a Key/Value Pair Array instead of just single values. Code is below:


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





Edited by - Jeepaholic on 06/24/2003 20:44:37

Edited by - Jeepaholic on 06/24/2003 20:46:12
Go to Top of Page
   

- Advertisement -