Getting the part of data from DB2 query



IBM's flagship relational database management system

Getting the part of data from DB2 query

Postby Balesh013GG » Thu Nov 22, 2012 10:20 pm

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!
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby NicC » Thu Nov 22, 2012 11:22 pm

Please show your attempt with SUBSTR.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Thu Nov 22, 2012 11:28 pm

select SUBSTR(PARM_VALUE,INSTR(PARM_VALUE,'(')+1,INSTR(PARM_VALUE,')')-INSTR((PARM_VALUE,'(')-1))..Here PARM_VALUE is the column name...
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby NicC » Fri Nov 23, 2012 5:59 pm

And what is the format of the SUBSTR function and its parameters?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Fri Nov 23, 2012 9:41 pm

would you please let me know where i am goign wrong or the other way to achieve this ? Thanks in advance...
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Fri Nov 23, 2012 11:08 pm

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

This is the syntax for SUBSTR...
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby GuyC » Mon Nov 26, 2012 4:48 pm

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 )
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Mon Nov 26, 2012 10:33 pm

Thanks lot. Its working fine for me now....
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby Balesh013GG » Wed Nov 28, 2012 11:59 pm

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
Balesh013GG
 
Posts: 20
Joined: Fri Oct 19, 2012 4:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Getting the part of data from DB2 query

Postby dick scherrer » Thu Nov 29, 2012 11:21 pm

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post