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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Dead Link in an Article
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jeepaholic
Starting Member

36 Posts

Posted - 06/24/2003 :  12:58:34  Show Profile  Visit Jeepaholic's Homepage  Reply with Quote
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

USA
4149 Posts

Posted - 06/24/2003 :  17:21:48  Show Profile  Visit graz's Homepage  Reply with Quote
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 - 06/24/2003 :  20:44:07  Show Profile  Visit Jeepaholic's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000