Is there a way to seperate a comma seperated string using Stored procedures?
moditha
I am passing a string like 1,2,3,4,5,6,7,8,9.... to a stored procedure...
i want to get value by value and do some operations. is there a way i can
use arrays or something
in T-SQL ??
whats the easy way of doing this??
regards
moditha
Mitchers
your string then you could look at the SUBSTRING FUNCTION
If you are passing a , delimited text file then if it's funky you want
then DTS would probably keep you occupied.
I don't know if you can pass a string to a DTS package from a third
party program and manipulate it from there though.
On Sat, 15 Apr 2000 19:10:09 +0530, "moditha" <mod...@mediasolv.com>
wrote:
BPMargolin
Arrays are not supported by SQL Server 7.0 (they are not part of the ANSI SQL-92
standard).
There is really no "easy" way to parse a string in T-SQL, although it can be
done using a fair amount of "grunt" coding. You might begin by checking out the
string manipulation functions available to you in T-SQL. Assuming you are using
SQL Server 7.0, begin by looking at section "String Functions (T-SQL)" in the
SQL Server 7.0 Books Online.
---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.
moditha <mod...@mediasolv.com> wrote in message
news:u0bEvutp$GA.279@cppssbbsa05...
Umachandar Jayachandran
your string contains say numbers from 0 to 1000 or something, you can do the
following:
CREATE TABLE Numbers ( Number smallint )
DECLARE @Number smallint
SELECT @Number = 0
WHILE @Number <= 1000
BEGIN
INSERT Numbers VALUES ( @Number )
SELECT @Number = @Number + 1
END
go
DECLARE @str varchar(255)
SELECT @Str = ',1,2,3,4,5,6,7,8,9,'
-- To get values out, do:
SELECT Number AS Value
FROM Numbers
WHERE CHARINDEX( ',' + CONVERT(varchar, Number ) + ',' , @Str ) > 0
These work wonderfully for simple lookup values passed from an
application. You can use the same trick to generate say all dates between 2
ranges, generate rows for summaries etc. The possibilities are endless.
--
Umachandar Jayachandran (UC)
SQL Resources at http://www.umachandar.com/resources.htm
Joe Celko
>> I am passing a string like 1,2,3,4,5,6,7,8,9.... to a stored
there a way I can use arrays or something in T-SQL ? <<
While this is not recommended at a technique, and it will vary from SQL
dialect to dialect, it is a good exercise in learning to think in
sets. You are given a quoted string which is made up of integers
separated by commas and your goal is to break each of integers out as a
row in a table. Do not hang in a loop pulling off one value at a
time!! That is procedural code and it will run orders of magnitude
slower than pure SQL.
The obvious approach is to write procedural code that will loop over
the input string and cut off all characters from the start up to, but
not including, the first comma each iteration. Here is an answer in
SQL/PSM
BEGIN DECLARE i INTEGER;
SET i = 1; -- iteration control variable
-- add sentinel comma to end of input string
SET inputstring = TRIM (BOTH '' FROM inputstring || ',')
WHILE i < CHAR_LENGTH(inputstring)
LOOP
WHILE SUBSTRING(inputstring, i, 1) <> ','
LOOP SET i = i + 1;
END LOOP;
SET outputstring = SUBSTRING(inputstring, 1, i-1);
INSERT INTO Outputs VALUES (CAST (outputstring AS INTEGER));
SET inputstring = SUBSTRING(inputstring, i+1);
END LOOP;
END;
Another way to do this is with an auxiliary table of sequential numbers
and this strange looking query.
INSERT INTO ParmList (parmeter_position, parameter)
SELECT S1.i,
CAST (SUBSTRING ((','|| :instring ||',')
FROM (S1.i + 1)
FOR (S2.i - S1.i - 1))
AS INTEGER)
FROM Sequence AS S1,
Sequence AS S2
WHERE SUBSTRING((','|| :instring ||',') FROM S1.i FOR 1) = ','
AND SUBSTRING((','|| :instring ||',') FROM S2.i FOR 1) = ','
AND S2.i
= (SELECT MIN(S3.i)
FROM Sequence AS S3
WHERE S1.i < S3.i
AND SUBSTRING((','|| :instring ||',')
FROM S3.i
FOR 1) = ',')
AND S1.i < CHAR_LENGTH (:instring + 1)
AND S2.i < CHAR_LENGTH (:instring + 2);
The trick here is to concatenate commas on the left and right sides of
the input string. To honest, you would probably want to trim blanks
and perhaps do other tests on the string, such as seeing that LOWER
(:instring) = UPPER(:instring) to avoid alphabetic characters, and so
forth. That edited result string would be kept in a local variable and
used in the INSERT INTO statement.
The integer substrings are located between the i-th and ((i+1)-th comma
pairs. In effect, the sequence table replaces the loop counter. The
Sequence table has to have enough numbers to cover the entire string,
but unless you really like to type in long parameter list, this should
not be a problem. The last two predicates are to avoid a Cartesian
product with the Sequence table.
--CELKO--
Joe Celko, SQL and Database Consultant
Sent via Deja.com http://www.deja.com/
Before you buy.
Philippe Dansereau
and thought it never would work outside of a costly cursor. Well, a
transfer task was just reduced by an order of magnitude...or three.
Thanks alot!
Philippe Dansereau
Umachandar Jayachandran <umach...@yahoo.com> wrote in message
news:e1niV9up$GA....@cppssbbsa02.microsoft.com...