Page 1 of 2

Getting the part of data from DB2 query

PostPosted: Thu Nov 22, 2012 10:20 pm
by Balesh013GG
I have a column X which has the data like " XXXX YYY=(ZZZZ,AAAA) ". I just wanted to know how we can get the data that is enclosed within paranthesis.i.e ZZZZ,AAAA. I tried with SUBSTR but its giving an error with error message illegal ',' operand. Please suggest me how should I proceed with. Thanks!

Re: Getting the part of data from DB2 query

PostPosted: Thu Nov 22, 2012 11:22 pm
by NicC
Please show your attempt with SUBSTR.

Re: Getting the part of data from DB2 query

PostPosted: Thu Nov 22, 2012 11:28 pm
by Balesh013GG
select SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR((PARM_VALUE,'(')-1))..Here PARM_VALUE is the column name...

Re: Getting the part of data from DB2 query

PostPosted: Fri Nov 23, 2012 5:59 pm
by NicC
And what is the format of the SUBSTR function and its parameters?

Re: Getting the part of data from DB2 query

PostPosted: Fri Nov 23, 2012 9:41 pm
by Balesh013GG
would you please let me know where i am goign wrong or the other way to achieve this ? Thanks in advance...

Re: Getting the part of data from DB2 query

PostPosted: Fri Nov 23, 2012 11:08 pm
by Balesh013GG
select substr(columnname,instr(columnname,"(") + 1, instr(columnname,")")) as temp from tablename

This is the syntax for SUBSTR...

Re: Getting the part of data from DB2 query

PostPosted: Mon Nov 26, 2012 4:48 pm
by GuyC
Wrong : SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR((PARM_VALUE,'(')-1))
Correct : SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR(PARM_VALUE,'(') - 1 )

Re: Getting the part of data from DB2 query

PostPosted: Mon Nov 26, 2012 10:33 pm
by Balesh013GG
Thanks lot. Its working fine for me now....

Re: Getting the part of data from DB2 query

PostPosted: Wed Nov 28, 2012 11:59 pm
by Balesh013GG
Guys sorry for interrupting you all.. But i am getting only one value that is enclosed within parantheses.

(xxxxx,yyyyy,zzzzz)... I am not getting the reslut for this data.

but getting the data where I have only (xxxxx)... Please suggest where I am going wrong and what should I do ? please

Re: Getting the part of data from DB2 query

PostPosted: Thu Nov 29, 2012 11:21 pm
by dick scherrer
Hello,

You need to post the actual data used and the sql code you executed. Use the Code tags to preserve alignment and improve readability.