loop and string split in stored procedure

[Mike Drewery]

How do I "split" a variable in a strored procedure?
The values can have as many as 10 items and as few as one.
I have values coming from an asp page and they look like this.

@Item_Nos = '22.1|22.2|'
@Units = 'fsd|sfd|'

I then want to loop through the insert

While something

Insert into Tab (Item_No, Unit) Values (x,y)

Loop

I have searched all over BOL and can't find it any help greatly appreciated.

Mike

[oj]

mike,

there is not split function in sql. however, you can use charindex() to
locate a character position, substring(), left(), and or right() to extract
the wanted info from a string.

-oj

"Mike Drewery" <Mi...@nowhere.com> wrote in message
news:OEtAaghbBHA.1988@tkmsftngp02...

[Neil White]

This is an example of something I did, prolly not the best, but it was never
used anyways :)

------------------------------------------------------------------------
DECLARE @End int
DECLARE @word varchar(8000)
DECLARE @sTemp varchar(255)
SELECT @End = 1
SELECT @word = ''
WHILE LEN(@String) > 0
BEGIN
SELECT @End = CHARINDEX (' ' , @String, 1)
If @End = 0 SELECT @End = Len(@String)
SELECT @sTemp = SUBSTRING(@String, 1, @End)
SELECT @sTemp = RTRIM(LTRIM(@sTemp))

SELECT @String = STUFF(@String, 1, @End, '')
If Not @sTemp = ''
BEGIN
If LEN(@word) > 0 SELECT @word = @word + ' OR '
SELECT @word = @word + 'k.csKeyword = ''' + LOWER(@sTemp) + ''' OR
(a.csAlternate = ''' + LOWER(@sTemp) + ''' AND a.csKeywordID = k.ID)'
END
END
------------------------------------------------------------------------

neil

"oj" <nospam...@home.com> wrote in message
news:#F$tAkhbBHA.1028@tkmsftngp04...

[Cletus D. Lee]

In article <OEtAaghbBHA.1988@tkmsftngp02>, Mi...@nowhere.com says...

Here is one that I just built for a CSV String:

WHILE PATINDEX('%,%', @freetext_columnlist) > 0
BEGIN
Select @freetext_column = SUBSTRING (
@freetext_columnlist , 0 , PATINDEX('%,%', @freetext_columnlist) )
Select @freetext_columnlist = LTRIM(RTRIM(SUBSTRING (
@freetext_columnlist , PATINDEX('%,%', @freetext_columnlist) + 1 ,LEN (
@freetext_columnlist ) )))
/* Do a bunch of things with @freetext_column */
END
SELECT @freetext_column = @freetext_columnlist

You need the last SELECT when there is only one item in your list or for
the last item in your list

--
Cletus Lee
School of Health Information Sciences
in The University of Texas Health Science Center at Houston

[Mike Drewery]

Thanks to all.

Mike

"Cletus D. Lee" <rben...@yahoo.com> wrote in message
news:MPG.165deec3f...@News.CIS.DFN.DE...

[Joe Celko]

Passing a list of parmeters to a stored procedure can be done by putting them into a string with a separator. I like to use the traditional comma. Let's assume that you have a whole table full of such parameer lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
...

This will be the table that gets the outputs, in the form of the original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(5) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end with a comma. You will also need a table called Sequence, which is a set of integers from 1 to (n).

SELECT keycol,
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1) - 1))
AS INTEGER),
COUNT(S2.seq) AS place
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' + I1.input_string + ',' , S1.seq, 1) = ','
AND SUBSTRING (',' + I1.input_string + ',' , S2.seq, 1) = ','
AND S1.seq < S2.seq
AND S2.seq <= LEN(I1.input_string) + 2
GROUP BY I1.keycol, I1.input_string, S2.seq
ORDER BY I1.keycol, I1.input_string, S2.seq

The S1 and S2 copies of Sequence are used to locate bracketing pairs of commas, and the entire set of substrings located between them is extracts and cast as integers in one non-procedural step. The trick is to be sure that the left hand comma of the bracketing pair is the closest one to the second comma. The place column tells you the relative position of the value in the input string

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

Mike,

There's an example script at
http://www.users.drew.edu/skass/SQL/ListParseExample.sql that will do what you
want. It assumes comma-separated strings, so change ',' to '|' and ',,' to '||'
in it.

Steve Kass
Drew University