Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: SQL / Parse a String

    Hi

    I have 1 database column PERSONDETAIL which includes in 1 field different attribut values seperated by ;

    Example:
    Muller;Andrew;Greenstreet 3;8356;Chicago; ;052 789 99 99;x.y@gmx.ch; ; ; ;

    How can I get the different values:
    Muller
    Andrew
    Greenstreet
    ...

    Can I build a function like getAttributvalue (PERSONDETAIL, 3)
    which returns the third value, in the example it would be Greenstreet

    Thank, Marc

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    You could probably use some combination of LOCATE() and SUBSTR() to do what you want. LOCATE the position of the ";" then use SUBSTR. Might needs some recursion to get to the next ";".
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    This one is too fun to pass up...

    Here's a start... it gets the 2nd element:

    WITH TMP_TAB (shortstring)
    AS ( SELECT SUBSTR(COL1, (LOCATE(';', COL1)+1)) from mytable)

    select SUBSTR(shortstring, 1, locate(';', shortstring)-1) from tmp_tab;

    Recursion could probably make this pretty slick, allowing you to get the 3rd, 4th, etc... good luck!

    (Or you could just write a UDF that uses Java or some other language that lends itself to this sort of thing)
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I think this might do it...

    Code:
    CREATE FUNCTION LOCATE_FIELD (DELIM CHAR(1), STRING VARCHAR(2000), FIELD_POS SMALLINT)
    RETURNS VARCHAR(100)
    LANGUAGE SQL
    DETERMINISTIC
    ------------------------------------------------------------------
    -- SQL function to return field from delimited string
    ------------------------------------------------------------------
    RETURN
    WITH CHAR_POSITIONS(INDEX, COUNTER) AS
    (
        VALUES (0, 0)
    
        UNION ALL
    
        SELECT COALESCE(LOCATE(DELIM, STRING, INDEX + 1), 0)
        ,      COUNTER + 1
        FROM   CHAR_POSITIONS
        WHERE  LOCATE(DELIM, STRING, INDEX + 1) != 0
          AND  COUNTER < FIELD_POS - 1
    )
    VALUES (
            CASE WHEN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS) < (FIELD_POS - 1)
              THEN CAST (NULL AS CHAR)
              ELSE SUBSTR ( STRING
                           ,(SELECT MAX(INDEX) FROM CHAR_POSITIONS) + 1
                           ,LOCATE( DELIM, STRING, (SELECT MAX(INDEX) FROM CHAR_POSITIONS) + 1  ) -
                            (SELECT MAX(INDEX) FROM CHAR_POSITIONS) - 1
                          )
            END
           )
    ;
    I haven't been able to test the above code because (a) there's something wrong with the database (b) there's something wrong with the code or (c) i'm being really stupid. ( I get a SQLSTATE=42884 )

    Conceptually, I reckon it's sound. The only proviso I envisage is that the input string should be terminated with the specified delimiter.

    If anyone gets this working and can tell me where I've gone wrong I'd like to know. (UDFs are not my strong point!)

    Damian

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Nice work...

    I did a quick test - only thing it doesn't handle as you said was that the last char of the string has to be the delimiter.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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