Hi,

How can i parse a string like this

I have a field coming in like
Jason,Mark,J


i need to parse it three fields using ',' seperator.

I know i can use locate or posstr in db2, but it only gives first occurence,
is there any other command where i can get 2nd ,3rd occurence of a string ',' in this case.

Thanks
Mark.

Here's a UDF to return a table 3 rows table using your input ..

CREATE FUNCTION CSV_TABLE(INLIST VARCHAR(1000))
RETURNS TABLE(LISTVAL CHAR(10))
BEGIN ATOMIC
RETURN
WITH TEMP1(STR1) AS
(
VALUES(REPLACE(INLIST||',''''','''',''))
)
,
TEMP2(STR3,STR4) AS
(
SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
UNION ALL
SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
)
SELECT STR3 FROM TEMP2 ;
END


Usage :


select upper(col1) from table(csv_table('London,Leeds,York')) tab1(col1)

returns

col1
-------
London
Leeds
York


HTH

Sathyaram

Thank You.