Let me cut&paste an old method that uses a comma as a delimter
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');
etc.
This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query,
CREATE VIEW ParmList (keycol, place, parm)
AS
SELECT keycol,
COUNT(S2.seq), -- reverse order
SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
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
GROUP BY I1.keycol, I1.input_string, S1.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
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. The relative position of each element
in the list is given by the value of "place", but it does a count down
so you can plan horizontal placement in columns.
Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks, handle
double commas etc. Basically, you must write part of a compiler in
SQL. Yeeeech! Or decide that you do not want to have data integrity,
which is what most Newbies do in practice altho they do not know it.
This is a job for the front end, which should be passing scalar values
to the RDBMS.
>> Stay informed about: Getting Data Out of Delimited List 
FAQ
Search
Profile
Private Messages
Log in
Not much is really new.