IBM Mainframe Forum

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!

Please show your attempt with SUBSTR.

select SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR((PARM_VALUE,'(')-1))..Here PARM_VALUE is the column name...

And what is the format of the SUBSTR function and its parameters?

would you please let me know where i am goign wrong or the other way to achieve this ? Thanks in advance...

select substr(columnname,instr(columnname,"(") + 1, instr(columnname,")")) as temp from tablename

This is the syntax for SUBSTR...

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 )

Thanks lot. Its working fine for me now....

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

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.