Results 1 to 3 of 3

Thread: parse a string.

  1. #1
    Join Date
    Jun 2004
    Posts
    46

    Unanswered: parse a string.

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    46
    Thank You.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •