parsing a comma-separatinf list

[AK]

Once upon a time there was a table:

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE CHAR(2))@

in a while the developers realized that a vendor may be present in
xseveral states, so the structure was changed to

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE_LIST
VARCHAR(150))@

STATE)LIST could contain a list of comma-separated values like this:
CA,WA,OR
Not the best approach, but out here in the field things like this
happen all the time. Everybody were absolutely happy until somebody
required a query like this:
SELECT * FROM VENDOR WHERE STATE IN ('IL', 'WI','MI')
SELECT COUNT(*), STATE FROM VENDOR GROUP BY STATE
and so on
Given a dayabase structure developed by a skilled professional, that
would be a piece of cake, but not this time.

Moving to a better structure

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50))@
CREATE TABLE VENDOR_IN_STATE (VENDOR_ID INT, STATE CHAR(2))@

nobody wanted to change the front end application.
As a workaround, I created a view and INSTEAD OF triggers that worked
like this:

INSERT INTO VENDOR_IN_STATE _VIEW VALUES(1, 'ABC INC.', 'AK,AL,IL')@

SELECT * FROM VENDOR
VENDOR_ID NAME
----------- --------------------------------------------------
1 ABC INC.
1 record(s) selected.

SELECT * FROM VENDOR_IN_STATE
VENDOR_ID STATE
----------- -----
1 AK
1 AL
1 IL
3 record(s) selected.

I used recursion to define both the view and the triggers:

CREATE FUNCTION PARSE_LIST(C_LI ST VARCHAR(100))
RETURNS TABLE(TOKEN VARCHAR(100))
SPECIFIC PARSE_LIST
RETURN
WITH PARSED_LIST(STE P, TOKEN, REMAINDER,LEFTM OST_COMMA)
AS(
VALUES(0, '',C_LIST,3)
UNION ALL
SELECT STEP+1 AS STEP,
CASE WHEN LEFTMOST_COMMA> 0 THEN
CAST(LEFT(REMAI NDER,LEFTMOST_C OMMA-1) AS CHAR(2))
ELSE
REMAINDER
END AS TOKEN,
CASE WHEN LEFTMOST_COMMA> 0 THEN
CAST(SUBSTR(REM AINDER,LEFTMOST _COMMA+1) AS VARCHAR(100))
ELSE
NULL
END AS REMAINDER,
LOCATE(',',SUBS TR(REMAINDER,LO CATE(',',REMAIN DER)+1)) AS
LEFTMOST_COMMA
FROM PARSED_LIST
WHERE REMAINDER IS NOT NULL
)
SELECT TOKEN FROM PARSED_LIST WHERE STEP>0

I wrapped the recursive query in UDF so that it could be reused:

It is very easy to invoke this table UDF:

SELECT * FROM TABLE(PARSE_LIS T('AK,AR,IL,OH' )) AS PARSE_LIST
TOKEN
------
AK
AR
IL

OH

I user the UDF in an INSTEAD OF trigger

CREATE TRIGGER VENDOR_IN_STATE _I
INSTEAD OF INSERT
ON VENDOR_IN_STATE _VIEW
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE VENDOR_FOUND INT;
SET VENDOR_FOUND=(S ELECT COUNT(*) FROM VENDOR WHERE NAME=N.NAME);
IF NOT(VENDOR_FOUN D>0)
THEN
INSERT INTO VENDOR(VENDOR_I D, NAME) VALUES (N.VENDOR_ID, N.NAME);
END IF;
INSERT INTO VENDOR_IN_STATE (VENDOR_ID, STATE)
SELECT N.VENDOR_ID, PARSE_LIST.TOKE N FROM
TABLE(PARSE_LIS T(N.STATE_LIST) ) AS PARSE_LIST;
END @

I would really appreciate any feedback.
Are there any simpler approaches?

[Knut Stolze]

AK <ak************ @yahoo.com> wrote:

Once upon a time there was a table:

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE CHAR(2))@

in a while the developers realized that a vendor may be present in
xseveral states, so the structure was changed to [...] I would really appreciate any feedback.
Are there any simpler approaches?

That looks pretty much straight forward to me and similar to what I wrote
once:
http://www-106.ibm.com/developerwork...03stolze1.html

The only alternative that might be a bit faster would be to use an external
table function, written in C or Java. That way, you could avoid the
recursion in SQL. I really don't know if performance would improve or not,
however.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[subaga]

why not keep the table as is (i.e. with comma separated states) and
create a view which will have the names of the states in different
rows. should be easier as u wont need a instead of trigger.

view can be created like

select vendor_id,subst r(state,locate( ',',state,a * 3)+1,2) from
vendor,table(va lues(1),(2),(3) ,(4),(5)) as vendor_ids(a) where a <=
length(state)/3

Knut Stolze <st****@de.ibm. com> wrote in message news:<bs******* ***@fsuj29.rz.u ni-jena.de>...

AK <ak************ @yahoo.com> wrote:

Once upon a time there was a table:

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE CHAR(2))@

in a while the developers realized that a vendor may be present in
xseveral states, so the structure was changed to

[...]

I would really appreciate any feedback.
Are there any simpler approaches?

That looks pretty much straight forward to me and similar to what I wrote
once:
http://www-106.ibm.com/developerwork...03stolze1.html

The only alternative that might be a bit faster would be to use an external
table function, written in C or Java. That way, you could avoid the
recursion in SQL. I really don't know if performance would improve or not,
however.

[Knut Stolze]

subaga <su******@yahoo .com> wrote:

why not keep the table as is (i.e. with comma separated states) and
create a view which will have the names of the states in different
rows. should be easier as u wont need a instead of trigger.

view can be created like

select vendor_id,subst r(state,locate( ',',state,a * 3)+1,2) from
vendor,table(va lues(1),(2),(3) ,(4),(5)) as vendor_ids(a) where a <=
length(state)/3

That only works if:
(1) the states are always exactly 2 characters long - if they are not, then
your calculation doesn't work.
(2) you have at most 5 states - ok, you could open this up by adding more
rows to "vendor_ids "; depending on the actual data, you might need several
1000 rows (VARCHARs can be up to 32K long!), and that might not be simpler
then.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

Knut,

thanks for answering! I loved your article!

[AK]

su******@yahoo. com (subaga) wrote in message news:<f6******* *************** **@posting.goog le.com>...

why not keep the table as is (i.e. with comma separated states) and
create a view which will have the names of the states in different
rows. should be easier as u wont need a instead of trigger.

view can be created like

select vendor_id,subst r(state,locate( ',',state,a * 3)+1,2) from
vendor,table(va lues(1),(2),(3) ,(4),(5)) as vendor_ids(a) where a <=
length(state)/3

In addition to what Knut has already writtenm,

if there is no table for states, htere are no indexes and statistics
on it, all the queries involving states run much slower. Also there
are quite a few books on the advantages of normalization, such as
written by Joe Celko

[--CELKO--]

>> in a while the developers realized that a vendor may be present in
several states, so the structure was changed to :

CREATE TABLE Vendors
(vendor_id INTEGER NOT NULL PRIMARY KEY,
vendor_name VARCHAR(35) NOT NULL,
state_list VARCHAR(150)NOT NULL);

STATE_LIST could contain a list of comma-separated values <<

Hopefully, someone took the idiot out to a wall, read him the part in
the most basic RDBMS book they could find about First Normal Form and
shot him. I like stories with happy endings.

Passing a list of parameters 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 parameter 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,89 6');
...

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).

CREATE VIEW Breakdown (keycol, parm_nbr, parameter)
AS
SELECT keycol, COUNT(S2.seq),
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1)))
AS INTEGER)
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_strin g) + 2
GROUP 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.

YOu can put this into a VIEW until you can fix the schema and perform
the execution.

[Daniel Morgan]

--CELKO-- wrote:

in a while the developers realized that a vendor may be present in

several states, so the structure was changed to :

CREATE TABLE Vendors
(vendor_id INTEGER NOT NULL PRIMARY KEY,
vendor_name VARCHAR(35) NOT NULL,
state_list VARCHAR(150)NOT NULL);

STATE_LIST could contain a list of comma-separated values <<

Hopefully, someone took the idiot out to a wall, read him the part in
the most basic RDBMS book they could find about First Normal Form and
shot him. I like stories with happy endings.

Passing a list of parameters 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 parameter 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,89 6');
...

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).

CREATE VIEW Breakdown (keycol, parm_nbr, parameter)
AS
SELECT keycol, COUNT(S2.seq),
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1)))
AS INTEGER)
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_strin g) + 2
GROUP 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.

YOu can put this into a VIEW until you can fix the schema and perform
the execution.

With the exception that I prefer vertical bars, or other characters that
couldn't legitimately be in the data stream I agree. I think commas have
too many common uses to be of value as a separator.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[AK]

Joe,

I loved the idea to use a sequence table, many thanks!
I would say that there is no need for aggregation either.
Given this table expression:

(SELECT
ROWNUMBER() OVER(ORDER BY S1.SEQ) AS SEPARATOR_NUM,
S1.SEQ AS SEPARATOR_POS
FROM SEQUENCE_TAB S1
WHERE
SUBSTRING (',' + I1.input_string + ',' , S1.seq, 1) = ','
) AS SEPARATORS

then we could self-join it like this:

SELECT
(S1.SEPARATOR_P OS + 1) AS TOKEN_FROM
(S2.SEPARATOR_P OS - S1.SEPARATOR_PO S - 1) AS TOKEN.LENGTH
FROM SEPATATORS S1
JOIN SEPARATORS S2
ON (S1.SEPARATOR_N UM + 1)=S2.SEPARATOR _NUM

What do you think?

[--CELKO--]

>> What do you think? <<

Neat but too proprietary for my taste. Remember, I am the guy who
pushes pure SQL-92 code.