Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Find a substr within a bracket

    i have query which has the following result in the column.

    Return Code (27)
    Return Code (46)
    Return Code (13)

    I am trying to extra the value inside the bracket to become

    27
    46
    13

    I tried to use the substr but the most I have get it

    27)
    46)
    13)

    Can someone helps? Thanks.

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    Try this:

    values ( substr('return code(27)',posstr('return code(27)','(')+1,posstr('return code(27)',')')-posstr('return code(27)','(')-1 ) )

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed repeated POSSTR functions.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT result_col
         , SUBSTR(result_col , lp_plus , POSSTR(result_col , ')') - lp_plus) AS extracted
     FROM  (VALUES 'Return Code (27)'
                 , 'Return Code (46)'
                 , 'Return Code (13)'
           ) result(result_col)
     CROSS JOIN LATERAL
           (VALUES POSSTR(result_col , '(') + 1 ) f(lp_plus)
    ;
    ------------------------------------------------------------------------------
    
    RESULT_COL       EXTRACTED       
    ---------------- ----------------
    Return Code (27) 27              
    Return Code (46) 46              
    Return Code (13) 13              
    
      3 record(s) selected.

  4. #4
    Join Date
    Jan 2004
    Posts
    5
    Hi thanks for all replies.

    @tonkuma, I tried your query but I got an error: An unexpected token "CROSS" was found following ") result(result_col)". Expected tokens may include: "<space>"

    @fengsun2, your query works without error, but I got:

    27
    27
    27

    but the result i need to get is:

    27
    46
    13

  5. #5
    Join Date
    Jan 2004
    Posts
    5
    I managed to get it already using fengsun2 query. Thanks!

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by ngaisteve1 View Post
    ...

    @tonkuma, I tried your query but I got an error: An unexpected token "CROSS" was found following ") result(result_col)". Expected tokens may include: "<space>"

    ...
    You must be using older DB2 version for LUW or DB2 for z/OS.

    If so, replace "CROSS JOIN" with traditional join syntax(" , ").

Posting Permissions

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