Find a substr within a bracket

ngaisteve1 · #1 (**permalink**) 01-09-12, 01:37

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.

fengsun2 · #2 (**permalink**) 01-09-12, 02:37

Try this:

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

tonkuma · #3 (**permalink**) 01-09-12, 03:03

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.

ngaisteve1 · #4 (**permalink**) 01-09-12, 04:25

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

ngaisteve1 · #5 (**permalink**) 01-09-12, 04:33

I managed to get it already using fengsun2 query. Thanks!

tonkuma · #6 (**permalink**) 01-09-12, 05:59

Quote:

Originally Posted by ngaisteve1

...

@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(" , ").

Internet Services

Web Development

Digital Marketing

Consumer Tech