Results 1 to 8 of 8

Thread: DB2 text field

  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: DB2 text field

    Hello,
    I have a db2 table with a text data type field. The data that this fields contain have the following format per row :" <entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry>
    <entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>" etc... . What i need is a statment that will select only the phone (111111).
    The lenght is not standart so i cannot use substring. I would be greatefull if you could help me with this.
    Thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by savvas1986 View Post
    The lenght is not standart so i cannot use substring.
    Yes you can, if you first use INSTR() or LOCATE() to find the starting position of a known string.

    You may also want to consider using pureXML features if your version of DB2 supports them.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    How could i do this with locate()? My string will always start after the "<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>"

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You published only one example(it's too little).
    So, I don't know what variations of data(including some exceptional cases) were in the column.

    Here is an example
    The query might be more complex depending on the existence of various variations.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(id , text) AS (
    VALUES
      ( 1 ,   '<entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry>'
           || '<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>'  )
    )
    SELECT id
         , SUBSTR(
              text
            , next_vector
            , LOCATE('</java.lang.String></Vector>' , text , next_vector)
              - next_vector
           ) AS phone
     FROM  sample_data
         , LATERAL
          (VALUES   LOCATE('<java.lang.String>PHONE</java.lang.String>' , text)
                  + LENGTH('<java.lang.String>PHONE</java.lang.String>')
          ) AS f(next_phone)
         , LATERAL
          (VALUES   LOCATE('<Vector> <java.lang.String>' , text , next_phone)
                  + LENGTH('<Vector> <java.lang.String>')
          ) AS f(next_vector)
    ;
    ------------------------------------------------------------------------------
    
    ID          PHONE                                                                                                                                                                                                                                        
    ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1 111111                                                                                                                                                                                                                                       
    
      1 record(s) selected.
    I don't know so much about pureXML features.
    Last edited by tonkuma; 03-22-12 at 09:54. Reason: Add id column to the result in sample code.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If "<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>" was fixed,
    the expression would be simpler, like
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(id , text) AS (
    VALUES
      ( 1 ,   '<entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry>'
           || '<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>'  )
    )
    SELECT id
         , SUBSTR(
              text
            , next_phone_vector
            , LOCATE('</java.lang.String></Vector>' , text , next_phone_vector)
              - next_phone_vector
           ) AS phone
     FROM  sample_data
         , LATERAL
          (VALUES   LOCATE('<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>' , text)
                  + LENGTH('<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>')
          ) AS f(next_phone_vector);
    ------------------------------------------------------------------------------
    
    ID          PHONE                                                                                                                                                                                                                                        
    ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              1 111111                                                                                                                                                                                                                                       
    
      1 record(s) selected.
    Last edited by tonkuma; 03-22-12 at 09:53. Reason: Add id column to the result in sample code.

  6. #6
    Join Date
    Mar 2012
    Posts
    3
    WITH
    sample_data(id , text) AS (
    VALUES
    ( 1 , '<entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry>'
    || '<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>' )
    )

    If i use this i guess i will have a sample data table with 1 row? maybe if i change the values with and set the id autogenerated and text=select myfield from mytable whould be better?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Needless to say, you can include id column into your SELECT statement to identify each rows.

    I'll change my sample codes.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    WITH
     sample_data(id , text) AS (
    VALUES
      ( 1 ,   '<entry><java.lang.String>NAME</java.lang.String><Vector> <java.lang.String>LALALALA</java.lang.String></Vector></entry>'
           || '<entry><java.lang.String>PHONE</java.lang.String><Vector> <java.lang.String>111111</java.lang.String></Vector></entry>'  )
    )
    This part is a way to generate test data(table with data).
    You can remove this code by executing "create table ..." and "insert into ..." statements before you execute the query.

Posting Permissions

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